Postgresql Tips

date: 2025-04-30

Introduction

Sometimes you want to recreate an postgresql database on a different server or postgresql instance. And so here are some tips how to do that.

Backup and Restore Using Tar File

create a backup with pg_dump

• switch to postgres user su - postgres
• create backup pg_dump -d mytestdb -F tar -f mytestdb.tar
• or with specific port pg_dump -p5432 -d mytestdb -F tar -f mytestdb.tar
• examine contents of tar tar -tvf mytestdb.tar
• alternately make a compressed backup
• pg_dump -d mytestdb -Fc -f mytestdb_compressed_backup

restore with pg_restore

• switch to postgres user su - postgres
• start psql psql -p5432
• postgres# CREATE USER mytestuser;
• postgres# \password mytestuser
• postgres# CREATE DATABASE mytestdb WITH OWNER mytestuser;
• list users postgres# \du
• list databases postgres# \l
• exit
• pg_restore -p5432 -d mytestdb mytestdb.tar
• or pg_restore -p5432 -d mytestdb mytestdb_compressed_backup

verify the restored database

• switch to postgres user su - postgres
• start psql psql -p5432
• change database postgres# \c mytestdb
• list tables postgres# \d or postgres# \dt

Backup and Restore Schema Only

create a backup with pg_dump

• switch to postgres user su - postgres
• create backup pg_dump --schema-only -d mytestdb -f mytestdb_schema.sql
• or with specific port pg_dump -p54342 --schema-only -d mytestdb -f mytestdb_schema.sql
• examine sql file less mytestdb_schema.sql
• note that the generated sql assigns ownership of each table, to that of the original database owner, upon creation

restore with psql command

• switch to postgres user su - postgres
• psql -p5432 -d mytestdb -f mytestdb_schema.sql

verify the restored database

• switch to postgres user su - postgres
• start psql psql -p5432
• change database postgres# \c mytestdb
• list tables postgres# \d or postgres# \dt