CODEWITHSUNDEEP
postgresql
darkpool
darkpool

Reputation: 14641

pg_restore works correctly but with some errors

I am testing my backup/restore process and when I restore the database all the tables are correct along with the data and table ownership. However there are some warnings/errors that I am concerned about.

My backup is run in a cronjob using the postgres crontab. However the pg_dump command within the postgres cronjob is run using the 'myuser' user:

00 01 * * * pg_dump -U myuser -Fc --exclude-table-data=example_table example_db > ~/backups/example_db_backup.gz

As you can see, I am excluding the data from one of the tables during the backup. All other tables etc are backed up normally.

I then create a new test database with the postgres user because I do not want to restore this to the live db. I grant full access to the new database for the 'myuser' user and then restore the db:

su postgres
psql -U postgres
create database test;
GRANT ALL PRIVILEGES ON DATABASE test TO myuser;
\q

I then restore the database as follows:

pg_restore -d test -U myuser ~/backups/example_db_backup.gz

As mentioned, the restore works and everything in the database appears to be correct, however during the restore I get the following messages:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2655; 0 0 COMMENT EXTENSION plpgsql 
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

pg_restore: WARNING:  no privileges could be revoked for "public"
pg_restore: WARNING:  no privileges could be revoked for "public"
pg_restore: WARNING:  no privileges were granted for "public"
pg_restore: WARNING:  no privileges were granted for "public"
WARNING: errors ignored on restore: 1

Are these errors something to be concerned about? Even though the restore appears to have worked correctly, I do not like ignoring potential issues which I don't understand.

Upvotes: 1

Views: 5828

Answers (1)

Mr Singh
Mr Singh

Reputation: 4220

I got the same error while loading sql dump file,

postgres=# alter role <user-name> superuser;

Provide superuser role to your postgres user,

Upvotes: 2

Related Questions