CODEWITHSUNDEEP
postgresqlpg-dumppg-restorepostgresql-10
raumi75
raumi75

Reputation: 351

postgresql pg_dump without schema name

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.

Upvotes: 25

Views: 14811

Answers (6)

Tatiana P
Tatiana P

Reputation: 21

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.

Upvotes: 2

Tim Nieradzik
Tim Nieradzik

Reputation: 309

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.

Upvotes: 0

Karl von Randow
Karl von Randow

Reputation: 484

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.

Upvotes: 2

pensnarik
pensnarik

Reputation: 1252

If you have Docker installed you can do a trick:

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

Upvotes: -3

raulchopi
raulchopi

Reputation: 338

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

set search_path to public;

Upvotes: -4

Oleg Korobov
Oleg Korobov

Reputation: 89

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

Upvotes: -4

Related Questions