Postgresql
PostgreSQL is a well known opensource database that scales well and is easy to use.
PostgreSQL 17 Installation
To install PostgreSQL 17,
apk add postgresql17 postgresql17-contrib postgresql17-openrc
rc-update add postgresql
rc-service postgresql start
This will start the postgresql server and perform some initial configuration. It will also create a system user called postgres.
PostgreSQL 16 Installation
To install PostgreSQL 16,
apk add postgresql16 postgresql16-contrib postgresql16-openrc
rc-update add postgresql
rc-service postgresql start
This will start the postgresql server and perform some initial configuration. It will also create a system user called postgres.
PostgreSQL 15 Installation
To install PostgreSQL 15,
apk add postgresql15 postgresql15-contrib postgresql15-openrc
rc-update add postgresql
rc-service postgresql start
This will start the postgresql server and perform some initial configuration. It will also create a system user called postgres.
PostgreSQL 14 Installation
To install PostgreSQL 14,
apk add postgresql14 postgresql14-contrib postgresql14-openrc
rc-update add postgresql
rc-service postgresql start
This will start the postgresql server and perform some initial configuration. It will also create a system user called postgres.
Configuration
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;
Network access
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.
In this example, we are using PostgreSQL v16, so substitute below for your specific version.
Edit the /etc/postgresql16/postgresql.conf file using nano or any other <editor> /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 /etc/postgresql16/pg_hba.conf file using nano or any other <editor> /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 restart
Allow the port through the firewall. For UFW firewall 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 that's beyond the scope of this documentation.
Switching between PostgreSQL versions
On Alpine Linux, you can use the command `pg_versions` to switch between PostgreSQL versions. This is very helpful when upgrading the version you are using.
Upgrading PostgreSQL
Please follow along with this guide and adjust the version you are moving from and the version you are upgrading to.
There's also this older guide.