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.

score 2 · Answer 1 · answered Jul 22 '20 at 01:31

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.

score 1 · Answer 2 · answered Mar 14 '18 at 16:34

1

I also searched this switcher and it's not exists. This behavior was added because of 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.

answered Mar 14 '18 at 16:34

Tatiana P

Which new variant? That would help answer the question. – Albert Vaca Cintora Jun 12 '18 at 11:55
2

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. – Tatiana P Jun 18 '18 at 10:49

score -2 · Answer 3 · answered Mar 04 '20 at 13:16

-2

If you have Docker installed you can do a trick:

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

answered Mar 04 '20 at 13:16

pensnarik

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` – wutzebaer Mar 27 '20 at 08:12

Oleg Korobov · Answer 4 · 2018-03-05T15:28:10.000

-3

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

edited Mar 05 '18 at 15:28

answered Mar 05 '18 at 15:23

Oleg Korobov

4

This wouldn't work, any table with values that happen to contain "public." would be incorrectly removed. – Alan Tan Mar 08 '18 at 03:15
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. – raumi75 Mar 12 '18 at 10:46

score -4 · Answer 5 · answered Feb 01 '19 at 08:40

-4

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

set search_path to public;

answered Feb 01 '19 at 08:40

raulchopi

5 Answers5