# Postgresql Tips - Trent's Blog
Trent's blog of mostly technical documentations.

Skip to content

# 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`