Error role does not exist PostgreSQL Fixed

How to Successfully Restore a PostgreSQL Database on a New Machine Without Role Conflicts

When migrating a PostgreSQL database between systems, one common error is the presence of non-existent roles. For example, you may encounter the error message ‘ERROR: role “linux” does not exist‘. This usually happens when the database you are attempting to restore contains references to a database role that does not exist on the destination machine. In this post, I will walk you through a simple solution to avoid the ERROR: role “linux” does not exist error when using ‘pg_dump‘ by specifying ‘–no-owner‘ and ‘–no-acl‘. This article might help you if you Unable to drop PostgreSQL database.

Background

Assume you have a PostgreSQL database named ‘linux‘ and a user (role) named ‘linux’. You want to move this database to another system where it should be named ‘unix‘ and the role ‘linux’ does not exist.

The Problem

Dumping and restoring the database using the standard ‘pg_dump‘ and ‘pg_restore‘ commands frequently results in the above-mentioned error because the role ‘linux‘ does not exist on the new system. This problem can halt the restoration process, necessitating manual intervention, which can be inconvenient.

The Solution

To avoid this error, modify the ‘pg_dump’ command to remove ownership and privilege information. This method makes the restoration process on another machine easier, particularly when the original roles do not exist or are not required.

Step 1: Dump the Database without Owner and ACL Information

Open your terminal and run the following command on the system that hosts the original database:

pg_dump -U linux -h localhost -p 5432 linux --no-owner --no-acl > linux.sql

-U linux specifies the user under which the command should run.

-h localhost points to the host where the database server is running.

-p 5432 is the port where PostgreSQL is listening.

–no-owner and –no-acl ensure that the dump file does not include any ownership or access control list information, which can cause problems if the role does not exist on the new machine.

Step 2: Restore the Database on the New System

After moving the ‘linux.sql’ file to the new system, you can restore it to a new database, which we will call ‘unix’. Run:

psql -h NEW_HOST -U NEW_USER -p 5432 -d unix < backup.sql

-U NEW_USER should be replaced with a user that exists on the new system.

-d unix specifies the target database.

-f backup.sql points to the dump file.

Conclusion

The ‘–no-owner’ and ‘–no-acl’ options for ‘pg_dump’ make it easier to migrate a PostgreSQL database between systems with different users or permissions. This approach avoids the time-consuming manual creation of matching roles on the new system, resulting in a smoother, error-free restoration.