PostgresSQL: Database Dump and Restore

adminkasep

Overview

Sometimes we need to dump and restore database postgres to other machine or into the other database name. In this time i have this task scenario.

  1. dump database comerce_prd and then restore to new database name commerce_dev
  2. create new role or username on postgres.
  3. reassign the owner schema and tables to new owner after restore

Configuration: Dump Database

We can dump database postgres with plaintext sql format to perform this task.

Bash
/usr/lib/postgresql/14/bin/pg_dump -x -d "postgresql://postgres:passwordPostgresUsers@localhost/commerce_prd" > commerce_prd.sql
  • -x = do not dump privileges (grant/revoke)
  • -d = connection to database postgres

Configuration: Restore Database

To restore the backup file with new database name we can use the following commands

Bash
# create new database name first
su - postgres
psql
create database commerce_dev
# restore file backup using psql command
psql -h localhost -W -d commerce_dev -U postgres < commerce_prd,sql

Configuration: Alter owner

After we restore the database with new database name the owner schema and tables is still with the old owner, we can alter the owner with new roles.

Bash
# create new role
create role admin_dev with login encrypted password '8e526548ac843048df2d885b4c69d67b';
# if you want to reset the password
alter user admin_dev with password 'newpassword';

We can check the existing owner schema and tables with these commands

Bash
# list of schema and the owner
\connect commerce_dev
\dn
# list of tables and the owner
\dt $schemaName.*
# to change the schema owner with the new role
\connect commerce_dev
alter schema $schemaName owner to admin_dev;

To reassign the owner table from schema we can use this command

Bash
# check current owner of table
\connect commerce_dev
\dt $schemaName.*

# To reaasign owner table we can use this command
select 'ALTER TABLE ' || t.tablename || ' OWNER TO commerce_dev;' 
from  pg_tables t
where schemaname = '$schemaName' and t.tableowner = '$oldowner';

# the result from above command is list of command to alter the owner, example the output
 ALTER TABLE business_entity OWNER TO commerce_dev;
 ALTER TABLE business_entity_role OWNER TO commerce_dev;
 ALTER TABLE custom_product OWNER TO commerce_dev;
 ALTER TABLE custom_product_addon OWNER TO commerce_dev;
 ALTER TABLE custom_product_image OWNER TO commerce_dev;
----
# we can copy and paste them in new console

we can copy and paste them in new console

Bash
set schema '$schemaName';
ALTER TABLE business_entity OWNER TO commerce_dev;
ALTER TABLE business_entity_role OWNER TO commerce_dev;
ALTER TABLE custom_product OWNER TO commerce_dev;
ALTER TABLE custom_product_addon OWNER TO commerce_dev;
ALTER TABLE custom_product_image OWNER TO commerce_dev;
# verify the table owner with this command
\dt $schemaName.*

Related Posts