Quick setup local postgresql for remote access

quickpsqlsetup.md

Quick setup local PostgreSQL for remote access

Needed to set up a local instance of postgresql on my home workstation for a project, but knew that I'd be accessing it remotely from a laptop on a regular basis (time away from the work area is important). Given my rule that "if you're going to do it more than once, document it", I decided to write up this gist.

Install postgresql

My workstation is on Ubuntu 20.04 LTS, so I first configured the package manager to use the appropriate postgresql.org repo:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Added the signing key:

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

When you run apt update, the system will warn you that apt-key has been deprecated. This is safe to ignore, at least for now.

Update and install:

$ sudo apt update

$ sudo apt install postgresql-14

NOTE: the package to install on any remote machine will be postgress-client-14.

Configure postgresql

Filesystem changes

I usually create a new database on my big disk to save space on root volume. Here we'll call that "/data1".

$ sudo mkdir -p /data1/pgsql/data
$ sudo chown postgres:postgres /data1/pgsql/data
$ sudo -u postgres /usr/lib/postgresql/14/bin/initdb -D /data1/pgsql/data

Then shutdown the server and edit /etc/postgresql/14/main/postgresql.conf to change the data_directory to this new location:

$ sudo systemctl stop postgresql
$ sudo vi /etc/postgresql/14/main/postgresql.conf
...
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/data1/pgsql/data'
...
:wq

Restart the server:

$ sudo systemctl start postgresql

Check your work:

$ sudo su - postgres -c psql
psql (14.4 (Ubuntu 14.4-1.pgdg20.04+1))
Type "help" for help.

postgres=# SHOW data_directory;
 data_directory 
----------------
 /data1/pgsql/data
(1 row)

Create superuser

Create a postgresql superuser for your remote sessions (in production I wouldn't do this, but this is going to be a dev environment).

First become the default postgres admin user:

$ sudo su - postgres

Connect to the database system:

$ psql 
psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1))
Type "help" for help.

postgres=#

Create the user (I named it for my system user account, "myuser"):

postgres=# CREATE ROLE myuser LOGIN SUPERUSER PASSWORD 'mysecretpassword';

Allow remote access

This will require making changes to two files under /etc/postgresql/13/main, pg_hba.conf and postgresql.conf.

Add the following "local" db admin entry to pg_hba.conf directly below the line for the existing postgres user:

local   all         myuser          peer

Then go down to the bottom of the file and add a new host entry:

host    all     all     10.1.1.0/24     md5

Substitute your local subnet for "10.1.1.0" in this example.

Now edit /etc/postgresql/13/main/postgresql.conf and change listen_addresses as follows:

listen_addresses = '*'

Test

Restart postgresql:

$ sudo systemctl restart postgresql

Try logging in (be sure to specify the database to connect to as below):

$ psql postgres
psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1))
Type "help" for help.

postgres=# 

Finally, get on a remote machine on your local network where you've installed psql (for Ubuntu this comes from the postgresql-client-13 package -- you don't need to install the server on your remote machines), and try that again:

$ psql -h 10.1.1.15 postgres
psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1))
Type "help" for help.

postgres=# 

If your postgres account doesn't have the same name as your logged in user you'll have to specify the name too:

$ psql -U myuser -h 10.1.1.15 postgres

That's it!

Now let the tirade from professional postgres admins begin...

To avoid a

N: Skipping acquire of configured file 'main/binary-i386/Packages' as repository 
'http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease' doesn't support 
architecture 'i386'

notice when updating the apt cache (sudo apt update), insert [arch=amd64] into the apt source file for Postgres:

/etc/apt/sources.list.d/pgdg.list

deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt jammy-pgdg main