Postgresql 16

General

PostgreSQL is a well known opensource database that scales well and is easy to use. In Alpine v3.20 we can install the latest version using the package postgresql16

Install

apk add postgresql16 postgresql16-contrib postgresql16-openrc

rc-update add postgresql

rc-service postgresql start

This will start the postgresql 16 server and perform some initial configuration.

Configure

Login as the postgres user and start psql to create a new user and database:

su postgres

psql

create user user with encrypted password 'password';

create database database;

grant all privileges on database database to user;

Use

By default only local access is allowed to PostgreSQL. To allow other networked services to access the database we need to configure PostgreSQL to allow external connections.

modify the postgresql.conf file

nano /etc/postgresql16/postgresql.conf

Find the line that starts with

#listen_addresses = 'localhost'

Uncomment it and change it to the following:

listen_addresses = '*'

If you want it to listen on a specific ip you can change * to 192.168.1.2/24. Save the file and change the next config file.

modify the pg_hba.conf file

nano /etc/postgresql16/pg_hba.conf

Look for the line:

host    all             all             127.0.0.1/32            md5

And change it to:

host all all 0.0.0.0/0 md5

This line allows connections from any IP address and requires a password for authentication (md5). Restart the server to allow incoming connections from other hosts.

rc-service postgresql start

Besure to allow the port through the firewall. For UFW type:

ufw allow 5432

This is a basic configuration. You can configure the PostgreSQL server to only allow certain networks or IP's to connect but thats beyond the scope of this documentation.

Troubleshooting

• Some troubleshooting information

Upgrading PostgreSQL

• Notes on upgrading PostgreSQL 16 to 17

Resources

• https://blog.devart.com/configure-postgresql-to-allow-remote-connection.html