Trying to replicate a database from one system to another. The versions involved are 9.5.0 (source) and 9.5.2 (target).
Source db name is foodb with owner pgdba and target db name will be named foodb_dev with owner pgdev.
All commands are run on the target system that will host the replica.
The pg_dump command is:
pg_dump -f schema_backup.dump --no-owner -Fc -U pgdba -h $PROD_DB_HOSTNAME -p $PROD_DB_PORT -d foodb -s --clean;
This runs without errors.
The corresponding pg_restore is:
pg_restore --no-owner --if-exists -1 -c -U pgdev -d foodb_dev schema_backup.dump
which throws error:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3969; 0 0 ACL public pgdba
pg_restore: [archiver (db)] could not execute query: ERROR: role "pgdba" does not exist
Command was: REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM pgdba;
GRANT ALL ON SCHEMA public TO pgdba;
GRANT ...
If I generate the dump file in plain text format (-Fp) I see it includes several entries like:
REVOKE ALL ON TABLE dump_thread FROM PUBLIC;
REVOKE ALL ON TABLE dump_thread FROM pgdba;
GRANT ALL ON TABLE dump_thread TO pgdba;
GRANT SELECT ON TABLE dump_thread TO readonly;
that try to set privileges for user pgdba who of course doesn’t even exist as a user on the target system which only has user pgdev, and thus the errors from pg_restore.
On the source db the privileges for example of the dump_thread table:
# dp+ dump_thread
Access privileges
-[ RECORD 1 ]-----+--------------------
Schema | public
Name | dump_thread
Type | table
Access privileges | pgdba=arwdDxt/pgdba+
| readonly=r/pgdba
Column privileges |
Policies |
A quick solution would be to simply add a user pgdba on the target cluster and be done with it.
But shouldn’t the --no-owner take care of not including owner specific commands in the dump in the first place?
Is there a way to export a PostgreSQL database and later import it with another name?
I’m using PostgreSQL with Rails and I often export the data from production, where the database is called blah_production and import it on development or staging with names blah_development and blah_staging. On MySQL this is trivial as the export doesn’t have the database anywhere (except a comment maybe), but on PostgreSQL it seems to be impossible. Is it impossible?
I’m currently dumping the database this way:
pg_dump blah > blah.dump
I’m not using the -c or -C options. That dump contains statements such as:
COMMENT ON DATABASE blah IS 'blah';
ALTER TABLE public.checks OWNER TO blah;
ALTER TABLE public.users OWNER TO blah;
When I try to import with
psql blah_devel < blah.dump
I get
WARNING: database "blah" does not exist
ERROR: role "blah" does not exist
Maybe the problem is not really the database but the role?
If I dump it this way:
pg_dump --format=c blah > blah.dump
and try to import it this way:
pg_restore -d blah_devel < tmp/blah.psql
I get these errors:
pg_restore: WARNING: database "blah" does not exist
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1513; 1259 16435 TABLE checks blah
pg_restore: [archiver (db)] could not execute query: ERROR: role "blah" does not exist
Command was: ALTER TABLE public.checks OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1509; 1259 16409 TABLE users blah
pg_restore: [archiver (db)] could not execute query: ERROR: role "blah" does not exist
Command was: ALTER TABLE public.users OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1508; 1259 16407 SEQUENCE users_id_seq blah
pg_restore: [archiver (db)] could not execute query: ERROR: role "blah" does not exist
Command was: ALTER TABLE public.users_id_seq OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1824; 0 0 ACL public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: role "postgres" does not exist
Command was: REVOKE ALL ON SCHEMA public FROM postgres;
pg_restore: [archiver (db)] could not execute query: ERROR: role "postgres" does not exist
Command was: GRANT ALL ON SCHEMA public TO postgres;
WARNING: errors ignored on restore: 11
Any ideas?
I’ve seen out there some people using sed scripts to modify the dump. I’d like to avoid that solution but if there are no alternative I’ll take it. Has anybody wrote a script to alter the dump’s database name ensure no data is ever altered?
I tried the command as you suggested but still had the same issue.
[postgres(at)server4 ~]$ pg_restore -p 5432 -d stgsample04
/var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3067; 0 0 ACL country
stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR: role
«sample_read_role» does not exist
Command was: REVOKE ALL ON TABLE country FROM PUBLIC;
REVOKE ALL ON TABLE country FROM stgsamplelogin01;
GRANT ALL ON TABLE country TO st…
pg_restore: [archiver (db)] Error from TOC entry 3068; 0 0 ACL
country_alias stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR: role
«sample_read_role» does not exist
Command was: REVOKE ALL ON TABLE country_alias FROM PUBLIC;
REVOKE ALL ON TABLE country_alias FROM stgsamplelogin01;
GRANT ALL ON TABLE c…
pg_restore: [archiver (db)] Error from TOC entry 3069; 0 0 ACL log
stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR: role
«sample_read_role» does not exist
Command was: REVOKE ALL ON TABLE log FROM PUBLIC;
REVOKE ALL ON TABLE log FROM stgsamplelogin01;
GRANT ALL ON TABLE log TO stgsamplelogin…
pg_restore: [archiver (db)] Error from TOC entry 3070; 0 0 ACL result
stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR: role
«sample_read_role» does not exist
Command was: REVOKE ALL ON TABLE result FROM PUBLIC;
REVOKE ALL ON TABLE result FROM stgsamplelogin01;
GRANT ALL ON TABLE result TO stgsa…
pg_restore: [archiver (db)] Error from TOC entry 3071; 0 0 ACL tariff
stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR: role
«sample_read_role» does not exist
Command was: REVOKE ALL ON TABLE tariff FROM PUBLIC;
REVOKE ALL ON TABLE tariff FROM stgsamplelogin01;
GRANT ALL ON TABLE tariff TO stgsa…
WARNING: errors ignored on restore: 5
[postgres(at)server4 ~]$ psql
psql (9.5.1)
Type «help» for help.
postgres=# connect stgsample04 stgsamplelogin01
You are now connected to database «stgsample04» as user «stgsamplelogin01».
stgsample04=> dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges
| Policies
———+——————-+——-+——————-+——————-+———-
public | country | table | |
|
public | country_alias | table | |
|
public | log | table | |
|
public | result | table | |
|
public | tariff | table | |
|
public | tariff_bk20140630 | table | |
|
(6 rows)
Regards,
Alexander Spiteri
On 10 March 2016 at 17:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alexander Spiteri <alexander(at)spiteri(dot)org> writes:
> > Please find a test case attached. I tried to put as much detail as i
> could.
>
> Okay, I see what’s going on. I really doubt that there is a regression
> here from 8.4 though. I will bet that the difference is you used plain
> text output from pg_dump back in 8.4, and now in 9.5 you are trying to
> use pg_restore direct-to-database mode.
>
> The proximate cause of the problem is that pg_dump bundles up GRANT/REVOKE
> statements for any object into a single «ACL» entry in the archive file.
> If you dump to SQL text this doesn’t matter because it just looks like a
> stream of separate statements. But if pg_restore is trying to restore
> direct to database then it issues the whole text of each archive file
> entry as a single PQexec() operation, which means that an error in one
> statement in that string causes the rest of ’em to be skipped.
>
> As a short-term workaround you could try restoring in this way:
>
> pg_restore foo.dump | psql
>
> To really fix it, I think we’d need either to expand «ACL» archive entries
> into a separate entry for each target user, or to change pg_restore to
> parse the entry contents into separate statements. The latter seems
> messy and bug-prone. The former solution wouldn’t fix the problem
> for dumps from existing pg_dump versions; but on the other hand, it might
> provide additional flexibility for selective restores, so I’m not sure
> that it’s a bad idea going forward.
>
> regards, tom lane
>