Backup & Transfer

Create a plain-text backup of the source database and move the file to the target server.

Backup Database (Windows)

Run this command in the Windows Run shell or command prompt to create a plain-text SQL dump file.

pg_dump -U postgres -Fp billing > billing_pg17.sql

Transfer and Move File

Move the transferred backup file to the temporary directory for easy access on the target machine.

sudo mv /home/user/Downloads/billing_pg17.sql /tmp/

File Permissions

Ensure the PostgreSQL system user has ownership and read permissions for the backup file.

Change Ownership and Permissions

Set owner to postgres and permissions to 644 (read/write for owner, read-only for others).

sudo chown postgres:postgres /tmp/billing_pg17.sql
sudo chmod 644 /tmp/billing_pg17.sql

Database & User Setup

Create the target database and a dedicated application user before restoring data.

Create Database and User

Switch to postgres user, open psql, and create the database and user.

sudo -i -u postgres psql

-- Inside psql console:
CREATE DATABASE billing;
CREATE USER billing_user WITH PASSWORD 'StrongPassword123';

Configure and Grant Permissions

Set environment configs and grant privileges. Transfer ownership to the new user.

ALTER ROLE billing_user SET client_encoding TO 'utf8';
ALTER ROLE billing_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE billing_user SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE billing TO billing_user;
ALTER DATABASE billing OWNER TO billing_user;

Database Restore

Restore the data into the newly created database.

Restore Data

Use psql to pipe the SQL file contents into the billing database.

# Switch to postgres user if not already
sudo -i -u postgres

# Restore the database
psql -d billing < /tmp/billing_pg17.sql

Final Permissions & Test

Ensure correct permissions for future objects and test the connection.

Re-apply Ownership and Privileges

Re-confirm ownership and grants after restoration.

ALTER DATABASE billing OWNER TO billing_user;
GRANT ALL PRIVILEGES ON DATABASE billing TO billing_user;

Set Default Schema Permissions

Ensure future tables in public schema are accessible by the user.

-- Connect to database
\c billing

-- Set future table permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO billing_user;

-- Set future sequence permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO billing_user;

-- Exit psql
\q
exit

Test User Connection

Verify login as the dedicated application user.

psql -U billing_user -d billing

Handle Peer Authentication Error

If "Peer Authentication" fails, enable password login in pg_hba.conf.

sudo nano /etc/postgresql/16/main/pg_hba.conf

# Add this line below 'local all postgres peer':
local   all             billing_user                            scram-sha-256

# Reload PostgreSQL
sudo systemctl reload postgresql