Postgresql backup and restore

Coming from a SQL server background. I used to never worry about the backup and restore database. Since I have a legacy product needs to maintain, I have to move the postgresql databased from one AWS account to another. So let’s get straight into it.

For backup, you can backup it to sql or a gzip file.

backup to sql

Pg_dump db_name > db_backup.sql
Pg_dumpall > cluster_backup.sql

backup to gzip

Pg_dump db_name | gzip > db_backup.gz
Pg_dumpall | gzip > cluster_backup.gz

Restore

pg_restore -v -h rds-postgres -U app_authentication_devint -d app_authentication *.dump --no-owner --role=app_authentication

Create a database with default credential

GRANT CONNECT ON DATABASE app_authentication_devint TO app_authentication; 

GRANT USAGE ON SCHEMA public TO app_authentication; 

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

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_authentication; 

Change user password

ALTER USER app_authentication_devint WITH ENCRYPTED PASSWORD 'password';