Error permission denied for database

Postgres databases can handle complex website functions easily.

But, what if you get a permission denied error for database Postgres, frustrating right?

This website error occurs due to the lack of database privileges like CONNECT, CREATE, etc.

At Bobcares, we often receive requests to fix the permission denied error as part of our Server Management Services.

Today, let’s discuss this error in detail and see how our Support Engineers fix it for our customers.

How to fix Permission denied for database Postgres?

Postgres is a powerful database that comes up with vast features to help developers.

But, it often shows up permission denied error. Finding the exact reason for this error can be quite tricky.

The core reason for the permission denied error in Postgres is the lack of several privileges.

Now, let’s see the main causes of this error and its respective fixes.

1. Missing CONNECT privilege

Recently, one of our customers approached us with a permission denied error in the Postgres. He tried to log in to his database using psql command,

psql userdb user --password

Here, userdb and user are the database name and username respectively.

But, after entering the password, he got the following error,

psql: FATAL: permission denied for database "userdb"
DETAIL: User does not have CONNECT privilege.

Our Support Engineers checked and found an error with CONNECT privilege.

Usually, the CONNECT privilege allows the user to connect to a database. And we check this privilege at the connection startup.

So, to grant CONNECT privilege, we followed the command,

GRANT CONNECT ON DATABASE userdb TO user ;

This resolved the error effectively.

2. Grant privileges to a new user

In some cases, users try to grant all privileges of a database to a new Postgres user other than the owner. For that, we use the command,

GRANT ALL PRIVILEGES ON DATABASE userdb TO new_user;

But, when we log in as the new user and try to read data from the table, it ends up showing the error,

ERROR: permission denied for relation table_name

This is because GRANT ALL PRIVILEGES ON DATABASE grants CREATE, CONNECT and TEMPORARY privileges on a database to a user.

But, none of these privileges permit the user to read data from the table.

Therefore, it requires the SELECT privilege. We resolve this permission denied error using the command.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

The new_user was then able to read data from the table.

Similarly, we can also resolve the permission denied error by setting DEFAULT privileges to the user.

Here, we use the ALTER DEFAULT PRIVILEGES command to define the default access privileges.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO new_user;

This will be specific to the schema specified in the command. Also, to apply it to the entire database, we use the command,

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO new_user;

3. Missing Postgres user

In the control panel based servers, permission denied error can happen due to missing users as well. For instance, in control panels like Plesk, if the password for PostgreSQL user postgres does not correspond password in the Plesk database, it shows up errors.

Therefore, to fix it, we update the PostgreSQL user with the proper password and sync it with the Plesk DB. And confirm it from Plesk panel at:

[Stuck with permission denied error in Postgres?- We’ll help you.]

Conclusion

In short, the permission denied for database Postgres occurs due to the lack of certain privileges like CONNECT, CREATE, DEFAULT and so on. In today’s writeup, we discussed how our Support Engineers fix Postgres privileges for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Easy way to fix permission denied for database Postgres error

Postgres databases can handle complex website functions easily.

But, what if you get a permission denied error for database Postgres, frustrating right?

This website error occurs due to the lack of database privileges like CONNECT, CREATE, etc.

At Bobcares, we often receive requests to fix the permission denied error as part of our Server Management Services.

Today, let’s discuss this error in detail and see how our Support Engineers fix it for our customers.

How to fix Permission denied for database Postgres?

Postgres is a powerful database that comes up with vast features to help developers.

But, it often shows up permission denied error. Finding the exact reason for this error can be quite tricky.

The core reason for the permission denied error in Postgres is the lack of several privileges.

Now, let’s see the main causes of this error and its respective fixes.

1. Missing CONNECT privilege

Recently, one of our customers approached us with a permission denied error in the Postgres. He tried to log in to his database using psql command,

Here, userdb and user are the database name and username respectively.

But, after entering the password, he got the following error,

Our Support Engineers checked and found an error with CONNECT privilege.

Usually, the CONNECT privilege allows the user to connect to a database. And we check this privilege at the connection startup.

So, to grant CONNECT privilege, we followed the command,

This resolved the error effectively.

2. Grant privileges to a new user

In some cases, users try to grant all privileges of a database to a new Postgres user other than the owner. For that, we use the command,

But, when we log in as the new user and try to read data from the table, it ends up showing the error,

This is because GRANT ALL PRIVILEGES ON DATABASE grants CREATE, CONNECT and TEMPORARY privileges on a database to a user.

But, none of these privileges permit the user to read data from the table.

Therefore, it requires the SELECT privilege. We resolve this permission denied error using the command.

The new_user was then able to read data from the table.

Similarly, we can also resolve the permission denied error by setting DEFAULT privileges to the user.

Here, we use the ALTER DEFAULT PRIVILEGES command to define the default access privileges.

This will be specific to the schema specified in the command. Also, to apply it to the entire database, we use the command,

3. Missing Postgres user

In the control panel based servers, permission denied error can happen due to missing users as well. For instance, in control panels like Plesk, if the password for PostgreSQL user postgres does not correspond password in the Plesk database, it shows up errors.

Therefore, to fix it, we update the PostgreSQL user with the proper password and sync it with the Plesk DB. And confirm it from Plesk panel at:

[Stuck with permission denied error in Postgres?- We’ll help you.]

Conclusion

In short, the permission denied for database Postgres occurs due to the lack of certain privileges like CONNECT, CREATE, DEFAULT and so on. In today’s writeup, we discussed how our Support Engineers fix Postgres privileges for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Источник

Permission denied for relation

I have ran the following SQL in psql:

When I login as bspu , and try to query the users table, I get the error:

I tried running:

But it doesn’t help. What am I doing wrong? Why does database owner NOT have permissions to query its own database?

EDIT: I upgraded bspu to superuser for now, so I can continue work. Any further guidance appreciated.

4 Answers 4

DEFAULT PRIVILEGES do not change permissions for existing objects. They are the default privileges for newly created objects and only for the particular role they belong to. If you do not specify the role when running ALTER DEFAULT PRIVILEGES , it defaults to the current role (when executing the ALTER DEFAULT PRIVILEGES statement.

Also, since you are using a serial column, which creates a SEQUENCE , you’ll want to set default privileges for sequences as well.

Run this on the user you create objects with, before you run the CREATE command:

A word of caution for pgAdmin users. There is a bug in all versions of pgAdmin III and pgAdmin4 (including v5.3). The reverse engineered SQL script for the database or schema nodes displays DEFAULT PRIVILEGES ignoring the owning user and is therefore incorrect in certain situations. I reported the bug (repeatedly), but the project encountered difficulties fixing it. pgAdmin III was discontinued in favor of pgAdmin4. The bug is still there in pgAdmin4.

For existing objects you may also be interested in this «batch» form of the GRANT command:

Источник

Permission denied for relation

I have ran the following SQL in psql:

When I login as bspu , and try to query the users table, I get the error:

I tried running:

But it doesn’t help. What am I doing wrong? Why does database owner NOT have permissions to query its own database?

EDIT: I upgraded bspu to superuser for now, so I can continue work. Any further guidance appreciated.

4 Answers 4

DEFAULT PRIVILEGES do not change permissions for existing objects. They are the default privileges for newly created objects and only for the particular role they belong to. If you do not specify the role when running ALTER DEFAULT PRIVILEGES , it defaults to the current role (when executing the ALTER DEFAULT PRIVILEGES statement.

Also, since you are using a serial column, which creates a SEQUENCE , you’ll want to set default privileges for sequences as well.

Run this on the user you create objects with, before you run the CREATE command:

A word of caution for pgAdmin users. There is a bug in all versions of pgAdmin III and pgAdmin4 (including v5.3). The reverse engineered SQL script for the database or schema nodes displays DEFAULT PRIVILEGES ignoring the owning user and is therefore incorrect in certain situations. I reported the bug (repeatedly), but the project encountered difficulties fixing it. pgAdmin III was discontinued in favor of pgAdmin4. The bug is still there in pgAdmin4.

For existing objects you may also be interested in this «batch» form of the GRANT command:

Источник

PROBLEM:

We have granted select privilege on one table of a schema to another user. Even after that the user was getting permission denied error while selecting data.

— Privilege was granted like this

Now connect to prim_user and run the select statement:

SOLUTION:

We need to provide usage privilege on that schema to other user also.

As per postgres note:

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema.

About Admin

Related Articles

• How to replicate tables in postgres using EDB replication server
• How to open postgres standby database for read write(snapshot standby)
• How to do switchover in postgres
• How to do failover in postgres
• How to configure streaming replication in postgres 14
• How to install postgres in linux using rpm
• Generate performance report in postgres using pg_profile
• How to upgrade postgres to new version

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Источник

Permission denied in Postgres [duplicate]

I added the user myuser to Postgres.

Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres .

Then I tried to give all rights to access and modify mydatabase to myuser . I logged into psql as user postgres :

and then I ran this query:

Now I can use myuser to log, but if I try a simple query I get this error:

Am I missing something? Can you help me solve that?

3 Answers 3

You’ve granted CREATE , CONNECT , and TEMPORARY privileges on the database to myuser but you haven’t granted SELECT and INSERT table privileges yet. You’ll need something like:

In addition you need privileges on sequences if you have any serial columns or other column defaults drawing from sequences. Generally, the USAGE privilege is be enough for INSERT operations to work, but since you asked to «give all rights» :

Use the command supplied by @Gord for already existing objects.

You will probably want to grant DEFAULT PRIVILEGES , too. So your user myuser can access future objects automatically, too.

Can be done per schema:

If you omit the schema, it applies to the whole database:

Only applies to objects created by the role specified (defaulting to the role that executes this command):

Available since PostgreSQL 9.0.
Don’t forget to GRANT privileges on SEQUENCES in addition if you have any. (For instance as source for default values in a serial column.)

While the other answers to this question are correct, allow me to propose another option. When a database is restored, you may be able to control the user the database is restored as. I am unsure about the specifics of how to do this with pgAdmin3, but the pg_dump and pg_restore utilities include specific options to allow this to be done.

If you use pg_dump —no-owner then the dump file will not include any ownership restoration. When you restore a dump created using —no-owner , the user you used to do the restore will own all of the objects. If you have a custom-format dump file and are using pg_restore, you can use the —no-owner option with pg_restore to have it skip over any ownership restoration when restoring the database.

Note that this will require the user doing the restore to have the necessary rights on the database to create the objects which exist in the dump file. Generally speaking, granting «CREATE» at the database level to the user doing the restore will be sufficient.

Источник

The first test to see whether you can access the database server is to try to create a database. A running PostgreSQL server can manage many databases. Typically, a separate database is used for each project or for each user.

Possibly, your site administrator has already created a database for your use. In that case you can omit this step and skip ahead to the next section.

To create a new database, in this example named mydb, you use the following command:

$ createdb mydb

If this produces no response then this step was successful and you can skip over the remainder of this section.

If you see a message similar to:

createdb: command not found

then PostgreSQL was not installed properly. Either it was not installed at all or your shell’s search path was not set to include it. Try calling the command with an absolute path instead:

$ /usr/local/pgsql/bin/createdb mydb

The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.

Another response could be this:

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

This means that the server was not started, or it is not listening where createdb expects to contact it. Again, check the installation instructions or consult the administrator.

Another response could be this:

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "joe" does not exist

where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. (PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 22 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.

If you have a user account but it does not have the privileges required to create a database, you will see the following:

createdb: error: database creation failed: ERROR:  permission denied to create database

Not every user has authorization to create new databases. If PostgreSQL refuses to create databases for you then the site administrator needs to grant you permission to create databases. Consult your site administrator if this occurs. If you installed PostgreSQL yourself then you should log in for the purposes of this tutorial under the user account that you started the server as. ^[1]

You can also create databases with other names. PostgreSQL allows you to create any number of databases at a given site. Database names must have an alphabetic first character and are limited to 63 bytes in length. A convenient choice is to create a database with the same name as your current user name. Many tools assume that database name as the default, so it can save you some typing. To create that database, simply type:

$ createdb

If you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb, you can destroy it using the following command:

$ dropdb mydb

(For this command, the database name does not default to the user account name. You always need to specify it.) This action physically removes all files associated with the database and cannot be undone, so this should only be done with a great deal of forethought.

More about createdb and dropdb can be found in createdb and dropdb respectively.

In part one, Migrating PostgreSQL Databases From On-Prem to the Cloud Using AWS RDS, we showed you how to perform the migration. In this blog, we will explore some common errors that might pop up during the migration.

BACKUP ERROR: Permission Denied to Backup Schema

/usr/pgsql-10/bin/pg_dump -v source_database -h onpremdbserver.domain.com -p 5432 -U source_appuser -Fd -j 100 -f /dbbackup/10/source_database   --no-owner --no-privileges
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for schema source_schema
pg_dump: [archiver (db)] query was: LOCK TABLE source_schema.table1 IN ACCESS SHARE MODE

RESOLUTION – Ensure source_appuser has the proper access. I.e. in this case, source_appuser is owner

postgres=> c source_database
psql (10.5, server 10.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "source_database" as user "postgres".
source_database=> dn+
                               List of schemas
     Name      |   Owner   |   Access privileges    |      Description       
---------------+-----------+------------------------+------------------------
 public        | postgres | postgres=UC/postgres+| standard public schema
               |           | =UC/postgres          |
 source_schema | postgres |                        |
(2 rows)
 
source_database=> alter schema source_schema owner to source_appuser ;
ALTER SCHEMA
 
source_database=> dt source_schema.table*
             List of relations
    Schema     |  Name  | Type  |   Owner  
---------------+--------+-------+-----------
 source_schema | table1 | table | postgres
 source_schema | table2 | table | postgres
 source_schema | table3 | table | postgres
(3 rows)
 
source_database=> alter table source_schema.table1 owner to source_appuser ;
ALTER TABLE
source_database=> alter table source_schema.table2 owner to source_appuser ;
ALTER TABLE
source_database=> alter table source_schema.table3 owner to source_appuser ;
ALTER TABLE
source_database=> dt source_schema.table*
                List of relations
    Schema     |  Name  | Type  |     Owner     
---------------+--------+-------+----------------
 source_schema | table1 | table | source_appuser
 source_schema | table2 | table | source_appuser
 source_schema | table3 | table | source_appuser
(3 rows)

BACKUP ERROR: Server Version Mismatch

# Backup database...
/usr/pgsql-10/bin/pg_dump -v source_database -h onpremdbserver.domain.com -p 5432 -U source_appuser -Fd -j 100 -f /dbbackup/10/source_database   --no-owner --no-privileges
pg_dump: server version: 10.6; pg_dump version: 9.6.9
pg_dump: aborting because of server version mismatch

RESOLUTION – Use correct PostgreSQL binary (i.e. /usr/pgsql-10/bin Instead of /usr/pgsql-9.6/bin)

# Backup database...
/usr/pgsql-10/bin/pg_dump -v source_database -h onpremdbserver.domain.com -p 5432 -U source_appuser -Fd -j 100 -f /dbbackup/10/source_database   --no-owner --no-privileges
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "source_schema.table1"
pg_dump: finding the columns and types of table "source_schema.table2"
pg_dump: finding the columns and types of table "source_schema.table3"
pg_dump: finding default expressions of table "source_schema.table3"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row security enabled for table "source_schema.table1"
pg_dump: reading policies for table "source_schema.table1"
pg_dump: reading row security enabled for table "source_schema.table2"
pg_dump: reading policies for table "source_schema.table2"
pg_dump: reading row security enabled for table "source_schema.table3_id_seq"
pg_dump: reading policies for table "source_schema.table3_id_seq"
pg_dump: reading row security enabled for table "source_schema.table3"
pg_dump: reading policies for table "source_schema.table3"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "source_schema.table1"
pg_dump: reading publication membership for table "source_schema.table2"
pg_dump: reading publication membership for table "source_schema.table3"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "source_schema.table1"
pg_dump: finished item 3797 TABLE DATA table1
pg_dump: dumping contents of table "source_schema.table3"
pg_dump: finished item 3800 TABLE DATA table3
pg_dump: dumping contents of table "source_schema.table2"
pg_dump: finished item 3798 TABLE DATA table2

RESTORATION ERROR: Must Be the Owner of Extension plpgsql

# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database   --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 3806 DATABASE source_database
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: processing item 3807 COMMENT SCHEMA public
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: creating SCHEMA "source_schema"
pg_restore: processing item 1 EXTENSION plpgsql
pg_restore: creating EXTENSION "plpgsql"
pg_restore: processing item 3808 COMMENT EXTENSION plpgsql
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3808; 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';
 
ERROR:  permission denied to create database

RESOLUTION: RDS does not allow true super user, but the error can be ignored.

RESTORATION ERROR: Permission Denied to Drop Database

ERROR:
postgres=> drop database dest_database_newdb ;
ERROR:  must be owner of database dest_database_newdb

RESOLUTION

postgres=> grant dest_rds_superuser to postgres ;
GRANT ROLE
postgres=> drop database dest_database_newdb ;
DROP DATABASE

RESTORATION ERROR: Permission Denied to Alter Role

# Grant dest_rds_superuser createdb...
/usr/pgsql-10/bin/psql -E -e postgres -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -Udest_rds_superuser -c"alter role dest_rds_superuser createdb"
alter role dest_rds_superuser createdb
ERROR:  permission denied

RESOLUTION

postgres=> grant rds_superuser to dest_rds_superuser ;
GRANT ROLE
 
 # Grant dest_rds_superuser createdb...
 /usr/pgsql-10/bin/psql -E -e postgres -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -Udest_rds_superuser -c"alter role dest_rds_superuser createdb"
 alter role dest_rds_superuser createdb
 ALTER ROLE

RESTORATION ERROR: Permission Denied to Create Database

ERROR:  permission denied to create database

RESOLUTION

RESOLUTION: Roles must have createdb privilege otherwise to avoid to the following error:
postgres=> alter role dest_rds_superuser createdb ;

RESTORATION ERROR: Database Already Exists

# Create database...
/usr/pgsql-10/bin/psql -E -e postgres -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -Udest_rds_superuser -c"create database dest_database_newdb"
create database dest_database_newdb
ERROR:  database "dest_database_newdb" already exists
# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database   --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 3806 DATABASE source_database
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: processing item 3807 COMMENT SCHEMA public
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: creating SCHEMA "source_schema"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6; 2615 20233 SCHEMA source_schema source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "source_schema" already exists
    Command was: CREATE SCHEMA source_schema;
       pg_restore: processing item 1 EXTENSION plpgsql
       pg_restore: creating EXTENSION "plpgsql"
       pg_restore: processing item 3808 COMMENT EXTENSION plpgsql
       pg_restore: creating COMMENT "EXTENSION plpgsql"
       pg_restore: [archiver (db)] Error from TOC entry 3808; 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: processing item 197 TABLE table1
       pg_restore: creating TABLE "source_schema.table1"
       pg_restore: [archiver (db)] Error from TOC entry 197; 1259 20234 TABLE table1 source_appuser
       pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table1" already exists
           Command was: CREATE TABLE source_schema.table1 (
           id integer
       );
       pg_restore: processing item 198 TABLE table2
       pg_restore: creating TABLE "source_schema.table2"
       pg_restore: [archiver (db)] Error from TOC entry 198; 1259 20237 TABLE table2 source_appuser
       pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table2" already exists
           Command was: CREATE TABLE source_schema.table2 (
           id integer
       );
       pg_restore: processing item 200 TABLE table3
       pg_restore: creating TABLE "source_schema.table3"
       pg_restore: [archiver (db)] Error from TOC entry 200; 1259 20242 TABLE table3 source_appuser
       pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table3" already exists
           Command was: CREATE TABLE source_schema.table3 (
           id integer NOT NULL,
           name character varying
       );
       pg_restore: processing item 199 SEQUENCE table3_id_seq
       pg_restore: creating SEQUENCE "source_schema.table3_id_seq"
       pg_restore: [archiver (db)] Error from TOC entry 199; 1259 20240 SEQUENCE table3_id_seq source_appuser
       pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table3_id_seq" already exists
           Command was: CREATE SEQUENCE source_schema.table3_id_seq
           AS integer
           START WITH 1
           INCREMENT BY 1
           NO MINVALUE
           NO MAXVALUE
           CACHE 1;
       pg_restore: processing item 3809 SEQUENCE OWNED BY table3_id_seq
       pg_restore: creating SEQUENCE OWNED BY "source_schema.table3_id_seq"
       pg_restore: processing item 3675 DEFAULT table3 id
       pg_restore: creating DEFAULT "source_schema.table3 id"
       pg_restore: entering main parallel loop
       pg_restore: launching item 3797 TABLE DATA table1
       pg_restore: launching item 3798 TABLE DATA table2
       pg_restore: launching item 3800 TABLE DATA table3
       pg_restore: launching item 3810 SEQUENCE SET table3_id_seq
       pg_restore: pg_restore: executing SEQUENCE SET table3_id_seq
       processing data for table "source_schema.table2"
       pg_restore: finished item 3798 TABLE DATA table2
       pg_restore: finished item 3810 SEQUENCE SET table3_id_seq
       pg_restore: processing data for table "source_schema.table3"
       pg_restore: processing data for table "source_schema.table1"
       pg_restore: finished item 3797 TABLE DATA table1
       pg_restore: finished item 3800 TABLE DATA table3
       pg_restore: finished main parallel loop
       WARNING: errors ignored on restore: 6

RESOLUTION – Ensure you manually remove existing database before executing the script.

# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database   --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 3806 DATABASE source_database
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: processing item 3807 COMMENT SCHEMA public
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: creating SCHEMA "source_schema"
pg_restore: processing item 1 EXTENSION plpgsql
pg_restore: creating EXTENSION "plpgsql"
pg_restore: processing item 3808 COMMENT EXTENSION plpgsql
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3808; 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: processing item 197 TABLE table1
pg_restore: creating TABLE "source_schema.table1"
pg_restore: processing item 198 TABLE table2
pg_restore: creating TABLE "source_schema.table2"
pg_restore: processing item 200 TABLE table3
pg_restore: creating TABLE "source_schema.table3"
pg_restore: processing item 199 SEQUENCE table3_id_seq
pg_restore: creating SEQUENCE "source_schema.table3_id_seq"
pg_restore: processing item 3809 SEQUENCE OWNED BY table3_id_seq
pg_restore: creating SEQUENCE OWNED BY "source_schema.table3_id_seq"
pg_restore: processing item 3675 DEFAULT table3 id
pg_restore: creating DEFAULT "source_schema.table3 id"
pg_restore: entering main parallel loop
pg_restore: launching item 3797 TABLE DATA table1
pg_restore: launching item 3798 TABLE DATA table2
pg_restore: launching item 3800 TABLE DATA table3 

RESTORATION ERROR: Schema Already Exists

# Create schema...
/usr/pgsql-10/bin/psql -E -e -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 dest_database_newdb -Udest_rds_superuser -c"create schema source_schema"
create schema source_schema
ERROR:  schema "source_schema" already exists
 
# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database --schema=source_schema  --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: processing item 197 TABLE table1
pg_restore: creating TABLE "source_schema.table1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 197; 1259 20234 TABLE table1 source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table1" already exists
    Command was: CREATE TABLE source_schema.table1 (
    id integer
);
 
 
 
pg_restore: processing item 198 TABLE table2
pg_restore: creating TABLE "source_schema.table2"
pg_restore: [archiver (db)] Error from TOC entry 198; 1259 20237 TABLE table2 source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table2" already exists
    Command was: CREATE TABLE source_schema.table2 (
    id integer
);
 
  
pg_restore: processing item 200 TABLE table3
pg_restore: creating TABLE "source_schema.table3"
pg_restore: [archiver (db)] Error from TOC entry 200; 1259 20242 TABLE table3 source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table3" already exists
    Command was: CREATE TABLE source_schema.table3 (
    id integer NOT NULL,
    name character varying
);
 
pg_restore: processing item 199 SEQUENCE table3_id_seq
pg_restore: creating SEQUENCE "source_schema.table3_id_seq"
pg_restore: [archiver (db)] Error from TOC entry 199; 1259 20240 SEQUENCE table3_id_seq source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table3_id_seq" already exists
    Command was: CREATE SEQUENCE source_schema.table3_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
 
 
pg_restore: processing item 3806 SEQUENCE OWNED BY table3_id_seq
pg_restore: creating SEQUENCE OWNED BY "source_schema.table3_id_seq"
pg_restore: processing item 3675 DEFAULT table3 id
pg_restore: creating DEFAULT "source_schema.table3 id"
pg_restore: entering main parallel loop
pg_restore: launching item 3797 TABLE DATA table1
pg_restore: launching item 3798 TABLE DATA table2
pg_restore: launching item 3800 TABLE DATA table3
pg_restore: launching item 3807 SEQUENCE SET table3_id_seq
pg_restore: pg_restore: processing data for table "source_schema.table2"
processing data for table "source_schema.table1"
pg_restore: executing SEQUENCE SET table3_id_seq
pg_restore: finished item 3797 TABLE DATA table1
pg_restore: finished item 3798 TABLE DATA table2
pg_restore: finished item 3807 SEQUENCE SET table3_id_seq
pg_restore: processing data for table "source_schema.table3"
pg_restore: finished item 3800 TABLE DATA table3
pg_restore: finished main parallel loop
WARNING: errors ignored on restore: 4

RESOLUTION – Either drop or rename existing schema. I like renaming until all is validated:

postgres=> c dest_database_newdb
psql (10.5, server 10.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "dest_database_newdb" as user "postgres".
dest_database_newdb=> dn
          List of schemas
     Name      |       Owner        
---------------+--------------------
 public        | postgres
 source_schema | dest_rds_superuser
(2 rows)
 
dest_database_newdb=> alter schema source_schema rename to source_schema_old;
ALTER SCHEMA
dest_database_newdb=> dn
            List of schemas
       Name        |       Owner        
-------------------+--------------------
 public            | postgres
 source_schema_old | dest_rds_superuser
(2 rows)
 
 
# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database --schema=source_schema  --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: processing item 197 TABLE table1
pg_restore: creating TABLE "source_schema.table1"
pg_restore: processing item 198 TABLE table2
pg_restore: creating TABLE "source_schema.table2"
pg_restore: processing item 200 TABLE table3
pg_restore: creating TABLE "source_schema.table3"
pg_restore: processing item 199 SEQUENCE table3_id_seq
pg_restore: creating SEQUENCE "source_schema.table3_id_seq"
pg_restore: processing item 3806 SEQUENCE OWNED BY table3_id_seq
pg_restore: creating SEQUENCE OWNED BY "source_schema.table3_id_seq"
pg_restore: processing item 3675 DEFAULT table3 id
pg_restore: creating DEFAULT "source_schema.table3 id"
pg_restore: entering main parallel loop
pg_restore: launching item 3797 TABLE DATA table1
pg_restore: launching item 3798 TABLE DATA table2
pg_restore: launching item 3800 TABLE DATA table3
pg_restore: launching item 3807 SEQUENCE SET table3_id_seq
pg_restore: processing data for table "source_schema.table1"
pg_restore: processing data for table "source_schema.table2"
pg_restore: executing SEQUENCE SET table3_id_seq
pg_restore: finished item 3807 SEQUENCE SET table3_id_seq
pg_restore: processing data for table "source_schema.table3"
pg_restore: finished item 3797 TABLE DATA table1
pg_restore: finished item 3798 TABLE DATA table2
pg_restore: finished item 3800 TABLE DATA table3
pg_restore: finished main parallel loop

Subscribe to get our best and freshest content