PostgreSQL

Installation & Configuration

Install Postgres

sudo apt install postgresql postgresql-contrib

Start the Postgres service

sudo systemctl start postgresql

Update Authentication method

By default, the postgres role is created with the authentication method set to peer for local connections. Update it to trust.

# Change version 16 in the path if applicable
sudo vi /etc/postgresql/16/main/pg_hba.conf

Make following changes:

local   all             postgres                                trust
host piratedev piratedev 192.168.1.0/24 md5

Enable Remote Connections

# Change version 16 in the path if applicable
sudo vi /etc/postgresql/16/main/postgresql.conf

Make following changes:

listen_addresses = '192.168.1.100'

Setup User and Database

Log into Postgres Shell as postgres user:

psql -U postgres

Create a new superuser and database:

-- Replace <Your Password> with a strong password of your choice
CREATE USER piratedev ENCRYPTED PASSWORD '<Your Password>';

CREATE DATABASE piratedev WITH OWNER=piratedev;

exit

Restart PostgreSQL Service:

sudo systemctl restart postgresql

Backup and Restore

On the remote server, dump the current database into a file using:

cd ~ && pg_dump -U postgres -d piratedev -f backup.sql

Copy the remote file to local using:

scp piratedev@192.168.1.100:/home/piratedev/backup.sql .

Restore the backup:

Make sure the DB user and database are created on new server before doing this.

psql -U postgres -d piratedev -f backup.sql