Alpine Linux: Installing & Configure Postgres
In this post, we're going to go through the steps to install and configure PostgreSQL on Alpine Linux 3.19.
Table of Contents
Installing PostgreSQL on Alpine Linux
Setting Up PostgreSQL
Initializing the Database Cluster
Configuring PostgreSQL for First Use
Starting and Checking the PostgreSQL Service
Starting the Service
Checking Service Status
Verifying Network Listener Settings with Netstat
DB Administration Tasks with a scenario
Switching to the PostgreSQL User
Accessing the PostgreSQL Shell
Creating a New Database and User
Database:
bookstoreUser:
bookstoreuser
Granting Privileges to the New User
Configuring PostgreSQL for Remote Access
Access the Database as a normal user
Before we start to install Postgres, we need to update & upgrade the current system: apk update & apk upgrade.
1. Installing postgres
apk add postgresql
Output:
alpine:~# apk add postgresql
(1/17) Installing postgresql-common (1.2-r0)
Executing postgresql-common-1.2-r0.pre-install
(2/17) Installing lz4-libs (1.9.4-r5)
(3/17) Installing libpq (16.2-r0)
(4/17) Installing readline (8.2.1-r2)
(5/17) Installing postgresql16-client (16.2-r0)
(6/17) Installing tzdata (2024a-r0)
(7/17) Installing icu-data-en (74.1-r0)
Executing icu-data-en-74.1-r0.post-install
*
* If you need ICU with non-English locales and legacy charset support, install
* package icu-data-full.
*
(8/17) Installing libgcc (13.2.1_git20231014-r0)
(9/17) Installing libstdc++ (13.2.1_git20231014-r0)
(10/17) Installing icu-libs (74.1-r0)
(11/17) Installing gdbm (1.23-r1)
(12/17) Installing libsasl (2.1.28-r5)
(13/17) Installing libldap (2.6.6-r1)
(14/17) Installing libxml2 (2.11.7-r0)
(15/17) Installing postgresql16 (16.2-r0)
Executing postgresql16-16.2-r0.post-install
*
* If you want to use JIT in PostgreSQL, install postgresql16-jit or
* postgresql-jit (if you didn't install specific major version of postgresql).
*
(16/17) Installing postgresql-common-openrc (1.2-r0)
(17/17) Installing postgresql16-openrc (16.2-r0)
Executing busybox-1.36.1-r15.trigger
Executing postgresql-common-1.2-r0.trigger
* Setting postgresql16 as the default version
OK: 217 MiB in 79 packages
2. Setting up Postgres
Run this command to setup:
/etc/init.d/postgresql setup
The setup process will be automatic, make sure it will be Success.
I ran into a problem while setting up the Postgres. The problem was the server's timezone, in the Postgres config file, it says
Asia//Ho_Chi_Minh, which is the wrong format of timezone. I have to change its value toAsia/Ho_Chi_Minh.
Here's the output:
alpine:~# /etc/init.d/postgresql setup
* Creating a new PostgreSQL 16 database cluster ...
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
Using language tag "en-001-x-icu" for ICU locale "en-001-x-icu".
The database cluster will be initialized with this locale configuration:
provider: icu
ICU locale: en-001-x-icu
LC_COLLATE: C
LC_CTYPE: C.UTF-8
LC_MESSAGES: C
LC_MONETARY: C
LC_NUMERIC: C
LC_TIME: C
The default text search configuration will be set to "english".
Data page checksums are enabled.
fixing permissions on existing directory /var/lib/postgresql/16/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Ho_Chi_Minh
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success.
3. Starting and checking the Postgres service
Start the Postgres service.
/etc/init.d/postgresql start
Check the status.
/etc/init.d/postgresql status
Netstat to check.
alpine:~# netstat -tlpun
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 4042/postgres
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 3330/sshd [listener
tcp 0 0 :::22 :::* LISTEN 3330/sshd [listener
tcp 0 0 ::1:5432 :::* LISTEN 4042/postgres
4. DB Administration Tasks with a scenario
Scenario
You're the admin of this DB server. You will create a new database named
bookstore, and a new user namedbookstoreuser. After that, you will assign the databasebookstoreto the newly created userbookstoreuser. You will also configure Postgres only allows userbookstoreusercan access this specific database from anywhere.
Switch to postgres user.
su - postgres
Then access the Postgres shell.
psql
Create database bookstore.
CREATE DATABASE bookstore;
Create user bookstoreuser, set its role is CREATEDB, and grant access database bookstore to bookstoreuser. After that, grant usage, and create on public schema to that user.
CREATE USER bookstoreuser WITH PASSWORD 'bookstorepw';
ALTER ROLE bookstoreuser CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE bookstore TO bookstoreuser;
// List users and roles
\du
List of roles
Role name | Attributes
---------------+------------------------------------------------------------
bookstoreuser | Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
bookstore=# GRANT USAGE ON SCHEMA public TO bookstoreuser;
GRANT
bookstore=# GRANT CREATE ON SCHEMA public TO bookstoreuser;
GRANT
5. Configuring Postgres for remote access
Configure Postgres only allows user bookstoreuser can access database bookstore from anywhere.
First, we need to quit Postgres and switch to root account.
postgres=# \q
alpine:~$ whoami
postgres
alpine:~$ su -
Password:
alpine:~# whoami
root
alpine:~#
Make some changes to two configuration files:
/var/lib/postgresql/16/data/postgresql.conf
listen_addresses = '*' tells Postgres to listen to all available interfaces.
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
- The second file is
/var/lib/postgresql/16/data/pg_hba.conf.
# local DATABASE USER METHOD [OPTIONS]
host bookstore bookstoreuser 0.0.0.0/0 md5
host bookstore bookstoreuser ::/0 md5
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostgssenc DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnogssenc DATABASE USER ADDRESS METHOD [OPTIONS]
The 0.0.0.0/0 is for IPv4, and ::/0 is for IPv6. This allows the user bookstoreuser to connect to the bookstore database from any IPv4 or IPv6 address, using MD5 password authentication.
That's it and restart the Postgres service.
service postgresql restart
* Stopping PostgreSQL 16 (this can take up to 15 seconds) ... [ ok ]
* Starting PostgreSQL 16 ... [ ok ]
Give it a check.
alpine:~# netstat -tlpun
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 4738/postgres
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 3396/sshd [listener
tcp 0 0 :::5432 :::* LISTEN 4738/postgres
tcp 0 0 :::22 :::* LISTEN 3396/sshd [listener
It seems ok, now acting as the user bookstoreuser, I will receive the credentials to manipulate the database bookstore on that server.
6. Access the Database as normal user
I will connect to the bookstore database with the credentials: user: bookstoreuser, password: bookstorepw.
Ignore the IP address, it depends on your network.
psql -h 192.168.43.112 -U bookstoreuser -d bookstore
Output:
psql -h 192.168.43.112 -U bookstoreuser -d bookstore
Password for user bookstoreuser:
psql (14.11 (Homebrew), server 16.2)
WARNING: psql major version 14, server major version 16.
Some psql features might not work.
Type "help" for help.
bookstore=>
Yes, I'm in. So far so good, I'm going to create a table named books, and some records in there.
bookstore=> CREATE TABLE books (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
description TEXT,
author VARCHAR(255)
);
CREATE TABLE
bookstore=>
bookstore=>
INSERT INTO books (name, description, author) VALUES ('The Great Gatsby', 'A story of the fabulously wealthy Jay Gatsby and his love for the beautiful Daisy Buchanan', 'F. Scott Fitzgerald');
INSERT INTO books (name, description, author) VALUES ('1984', 'A dystopian novel by English novelist George Orwell', 'George Orwell');
INSERT INTO books (name, description, author) VALUES ('To Kill a Mockingbird', 'A novel by Harper Lee published in 1960', 'Harper Lee');
Try to select.
id | name | description | author
----+-----------------------+--------------------------------------------------------------------------------------------+---------------------
1 | The Great Gatsby | A story of the fabulously wealthy Jay Gatsby and his love for the beautiful Daisy Buchanan | F. Scott Fitzgerald
2 | 1984 | A dystopian novel by English novelist George Orwell | George Orwell
3 | To Kill a Mockingbird | A novel by Harper Lee published in 1960 | Harper Lee
(3 rows)
That's all, and hope this helps!