Personal Docs

Toggle Theme

PostgreSQL ​

Installation & Configuration ​

Install Postgres ​

shell
sudo apt install postgresql postgresql-contrib

Start the Postgres service ​

shell
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.

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

Make following changes:

shell
local   all             postgres                                trust
host piratedev piratedev 192.168.1.0/24 md5

Enable Remote Connections ​

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

Make following changes:

shell
listen_addresses = '192.168.1.100'

Setup User and Database ​

Log into Postgres Shell as postgres user:

shell
psql -U postgres

Create a new superuser and database:

sql
-- 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:

shell
sudo systemctl restart postgresql

Backup and Restore ​

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

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

Copy the remote file to local using:

shell
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.

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