postgresql pg_dump without schema name

Question

pg_dump version 10.3 started adding the schema name explicitly to the table name. So what used to be

INSERT INTO table (id) VALUES (1);

Is now

INSERT INTO public.table (id) VALUES (1);

How can you switch it off? My problem is, that I dump this into a mariadb where you don't have schema names.

Iridescent

Answer 1

I also searched for a way to switch the schema name, but it turns out there is none. The reason is this: https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

In my work I now use new variant of dump with renaming schemas in transaction.

Sphery

Which new variant? That would help answer the question. – Huguenot

Variant that created by pg_dump new vertion - with schema names in queries. Answer the question is "It can't be switch off in patched vertions of pg_dump". Use old vestions or rename schema to "big_special_name_for_replace" before dumping and replace this name in dump file. – Sphery

Answer 2

If it's just the INSERT statements that you care about, like me, then I believe this is safe, and will remove the "public" schema from your tables.

sed -i -e 's/^INSERT INTO public\./INSERT INTO /' <filename.sql>

It modifies the file in place, so be careful.

Sisterhood

Answer 3

With a few modifications to the pg_dump sources, you can restore the previous behaviour such that INSERT statements do not include the schema name.

To build a Docker image with these changes, create a Dockerfile with the following content:

FROM alpine:latest

WORKDIR /usr/src/postgresql

RUN apk add --no-cache \
    alpine-sdk \
    perl \
    readline-dev \
    zlib-dev \
    bison \
    flex \
    git \
    coreutils \
    linux-headers \
    util-linux-dev

RUN git clone \
    --depth 1 \
    --branch feat/schema-less-pg-dump-11 \
    https://github.com/tindzk/postgres.git .

RUN ./configure && make

RUN cp src/bin/pg_dump/pg_dump /usr/bin/ && \
    cp ./src/interfaces/libpq/libpq.so* /usr/lib/

RUN apk del alpine-sdk \
    && rm -rf /var/cache/apk/* /usr/src/postgresql

CMD ["pg_dump"]

Afterwards, use it as follows:

docker build -t pgdump-11 .

docker run \
  -e PGPASSWORD=$db_password \
  -v "$(pwd)":/data/ \
  pgdump-11 \
  pg_dump \
  --verbose \
  "postgres://$db_user@$db_host:$db_port/$db_name" \
  --schema $db_schema \
  --file /data/dump.sql

This approach is more robust than pattern matching and avoids the risk of false positives.

Wera

Answer 4

If you have Docker installed you can do a trick:

docker run -it postgres:10.2 pg_dump postgresql://user:pass@host/database

Cassidycassie

doesnt wotrk with newer dbs pg_dump: server version: 12.2; pg_dump version: 10.2 (Debian 10.2-1.pgdg90+1) pg_dump: aborting because of server version mismatch – Sternutation

Answer 5

Of course it's just linux decision (not postgres), but it helps you i suppose

sed -i 's/public.//g' your_dmp_file

P.s. I didn't check this, may be you should fix it

Thirza

This wouldn't work, any table with values that happen to contain "public." would be incorrectly removed. – Vespine

Thank you. That's actually what I have done, because I'm sure there is no data containing the string 'public.' However it's kind of ugly. I'm surprised there is no proper way to switch this off. – Iridescent

Answer 6

Execute this command in your postgresql console (connected to your database):

set search_path to public;

Nordic

There are no search paths in MySQL. – Cassidycassie

@Cassidycassie the question was PostgreSQL specific... – Fachini