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 myuser with encrypted password 'mypass';
create database mydb;
grant all privileges on database mydb to myuser;
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 localhost to *:
listen_addresses = '*'
or change it to the IP address you want the server to listen on. 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).
Troubleshooting
- Some troubleshooting information
Upgrading PostgreSQL
- Notes on upgrading PostgreSQL 16 to 17