I’m writing this so that I don’t have to keep remembering how to do this.

Setting up PostgreSQL on Linode and accessing it remotely from your development machine is straightforward. Follow these steps:

1. Install PostgreSQL on Linode

sudo apt update
sudo apt install postgresql postgresql-contrib net-tools

2. Set Up Your Database and User

Switch to the PostgreSQL user:

sudo -i -u postgres
createdb your_database_name
psql

Create a new user and grant privileges:

CREATE USER your_user_name WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_user_name;
\q

Exit the PostgreSQL user:

exit

3. Install the PostgreSQL Adapter in Your Virtual Environment

Activate your virtual environment and install the adapter:

source activate venv
pip install psycopg2-binary

4. Configure PostgreSQL for Remote Access

Edit the postgresql.conf file to listen on the Linode server’s IP:

sudo nano /etc/postgresql/15/main/postgresql.conf

Update the listen_addresses line:

listen_addresses = 'localhost,<ip_of_linode>'

Edit the pg_hba.conf file to allow connections from your development machine:

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

Add the following line (use spaces, not tabs):

host    all             all             your_local_ip_address/32    md5

5. Restart PostgreSQL and Verify Configuration

Restart PostgreSQL to apply the changes:

sudo systemctl restart [email protected]
sudo systemctl status [email protected]

Allow connections through the firewall:

sudo ufw allow 5432/tcp

Verify that PostgreSQL is listening on the correct IP and port:

sudo netstat -plnt | grep postgres

6. Connect from Your Development Machine

psql -h your_linode_ip_address -U your_user_name -d your_database_name