Menu
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.
663
One caveat: pg_dump does not dump roles or other database objects including tablespaces, only a single database. To take backups on your entire PostgreSQL cluster, pg_dumpall is the better choice. pg_dumpall can handle the entire cluster, backing up information on roles, tablespaces, users, permissions, etc…
If you want to dump data from postgres into another database then dump as INSERT statements by using pg_dump -D DB_NAME > /tmp/data_dump_as_inserts This uses the INSERT INTO table (col1, col2) VALUES (val1, val2) format.
pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).
When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema (s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database. Non-schema objects such as blobs are not dumped when -n is specified.
Re: remapping schema names with pg_restore? As noted, there's no direct support in pg_dump, psql or pg_restore to change the schema name during a dump/restore process. But it's fairly straightforward to export using "plain" format then modify the .sql file.
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.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
