Postgres DB Dump Carries Owner Information With It-CodePudding

I am setting up a system of DB backup for a web project. At the moment, the DB is being backed up via Bash script like this

#!/bin/bash
# Set the DB configuration
export PGHOST=localhost
export PGPORT=5432
#export PGDATABASE=yoga
#export PGUSER=delme
export PGDATABASE=projectA
export PGUSER=userA
export PGPASSWORD=blah-blah

# Backs up mydatabase to a file and then uploads it to AWS S3.
# First, dump database backup to a file
TIME=$(date " %s")
BACKUP_FILE="postgres_${PGDATABASE}_${TIME}.pgdump"
echo "Backing up $PGDATABASE to $BACKUP_FILE"
pg_dump --format=custom > $BACKUP_FILE

In the cluster, I have the following roles

 Role name |                         Attributes                         | Member of 
----------- ------------------------------------------------------------ -----------
 delme     | Superuser                                                  | {}
 userA     |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

The list of databases

postgres=# \list
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
----------- ---------- ---------- --------- --------- -----------------------
 projectA  | userA    | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/userA           
           |          |          |         |         | userA=CTc/userA
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres           
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres           
           |          |          |         |         | postgres=CTc/postgres
 yoga      | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
(5 rows)

Whenever I bring this dump on a local machine and try to restore via pg_restore --host=localhost --port=5432 --username=postgres --verbose --create --dbname=new_db_name --format=custom --clean postgres_projectA_1660909386.pgdump, I get about 200 errors.

Most errors are of the following format:

pg_restore: creating SEQUENCE OWNED BY "public.plugins_mentorsplugin_mentors_id_seq"
pg_restore: creating TABLE "public.plugins_plugingeneral"
pg_restore: from TOC entry 374; 1259 19889 TABLE plugins_plugingeneral delme
pg_restore: error: could not execute query: ERROR:  role "delme" does not exist
Command was: ALTER TABLE public.plugins_plugingeneral OWNER TO delme;

pg_restore: creating TABLE "public.plugins_retreatcommentplugin"
pg_restore: from TOC entry 375; 1259 19892 TABLE plugins_retreatcommentplugin delme
pg_restore: error: could not execute query: ERROR:  role "delme" does not exist
Command was: ALTER TABLE public.plugins_retreatcommentplugin OWNER TO delme;

pg_restore: creating TABLE "public.plugins_retreatcommentplugin_comments"
pg_restore: from TOC entry 376; 1259 19895 TABLE plugins_retreatcommentplugin_comments delme
pg_restore: error: could not execute query: ERROR:  role "delme" does not exist
Command was: ALTER TABLE public.plugins_retreatcommentplugin_comments OWNER TO delme;

pg_restore: creating SEQUENCE "public.plugins_retreatcommentplugin_comments_id_seq"
pg_restore: from TOC entry 377; 1259 19898 SEQUENCE plugins_retreatcommentplugin_comments_id_seq delme
pg_restore: error: could not execute query: ERROR:  role "delme" does not exist
Command was: ALTER TABLE public.plugins_retreatcommentplugin_comments_id_seq OWNER TO delme;

pg_restore: creating SEQUENCE OWNED BY "public.plugins_retreatcommentplugin_comments_id_seq"
pg_restore: creating TABLE "public.plugins_retreatdayplan"
pg_restore: from TOC entry 378; 1259 19900 TABLE plugins_retreatdayplan delme
pg_restore: error: could not execute query: ERROR:  role "delme" does not exist
Command was: ALTER TABLE public.plugins_retreatdayplan OWNER TO delme;

In short, the restoration does not work, so my thinking is to troubleshoot these errors. What is causing the Postgres to bring this information into the dump, despite my creating a new database with a new connection user? How can I make these errors go away?

CodePudding user response：

The --no-owner flag prevents writing ownership when used with pg_dump.

Alternatively use --no-owner with pg_restore to avoid restoring ownership. All objects created will then belong to the user that was used to run pg_restore (in your example all of them would belong to postgres)