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?
>Solution :
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)