AlpineLinux installing 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: bookstore
  - User: 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 to Asia/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 named bookstoreuser. After that, you will assign the database bookstore to the newly created user bookstoreuser. You will also configure Postgres only allows user bookstoreuser can 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!

sudacode's blog

sudacode's blog

Alpine Linux: Installing & Configure Postgres

1. Installing `postgres`

2. Setting up Postgres

3. Starting and checking the Postgres service

4. DB Administration Tasks with a scenario

Scenario

5. Configuring Postgres for remote access

6. Access the Database as normal user

Alpine Linux: Installing & Configure Postgres

1. Installing postgres

2. Setting up Postgres

3. Starting and checking the Postgres service

4. DB Administration Tasks with a scenario

Scenario

5. Configuring Postgres for remote access

6. Access the Database as normal user

1. Installing `postgres`