PostgreSQL

#lang-c #database 

PostgreSQL is a free and object-relational database system. It is also compatible to the familiar SQL standard. More details are available in the Wikipedia.

Some projects (e.g. Miniflux2 and Matrix) require PostgreSQL and many others support it as an alternative to MySQL.

License

PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

Installation

Step 1 - Download and Extract the Source Code

Create a working directory.

[isabell@stardust ~]$ mkdir ~/postgres/
[isabell@stardust ~]$ cd ~/postgres/
[isabell@stardust ~]$

Download the latest stable version. A list of supported major and beta releases can be found on the PostgreSQL download server.

[isabell@stardust ~]$ curl -O https://download.postgresql.org/pub/source/v12.4/postgresql-12.4.tar.gz
[isabell@stardust ~]$

To extract the tar archive, use the following options:

  • -x: To extract files and directories.

  • -v: To have a verbose output.

  • -z: To consider gzip.

  • -f: And as last option the file to extract.

[isabell@stardust ~]$ tar -xvzf ~/postgres/postgresql-12.4.tar.gz
[isabell@stardust ~]$

Step 2 - Source Code Configuration, Compiling and Installation

Before we start, we have to consider some aspects, e.g. Python support, and corresponding settings, regarding to a shared hosting environment like Uberspace:

  • --prefix=$HOME/opt/postgresql/: New installation target for your personal Uberspace.

  • --with-python PYTHON=/usr/bin/python3: Compiling with Python 3.x support. Alternatively you can choose /usr/bin/python2 or any other version.

  • --without-readline: In case of problems, regarding missing Readline support, you can exclude Readline with this option.

Other options can be found in the PostgreSQL documentation.

Now configure and compile the source code and finally install it.

Note

Please use single steps instead of combining all three in one process to see and identify possible errors.

Important

For future usage with projects like Miniflux2, ejabberd, Matrix etc. it is recommended to consider to install all modules. This is done by using the target world for make and make install.

[isabell@stardust ~]$ cd ~/postgres/postgresql-12.4
[isabell@stardust ~]$ ./configure --prefix=$HOME/opt/postgresql/
[isabell@stardust ~]$ make world
[isabell@stardust ~]$ make install-world
[isabell@stardust ~]$

Step 3 - Environment Settings

Please add the following lines to your ~/.bash_profile:

# Postgresql Environment

export PATH=$HOME/opt/postgresql/bin/:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/opt/postgresql/lib
export PGPASSFILE=$HOME/.pgpass

Reload the .bash_profile with:

[isabell@stardust ~]$ source ~/.bash_profile
[isabell@stardust ~]$

Run psql --version to verify the installation so far:

[isabell@stardust ~]$ psql --version
psql (PostgreSQL) 12.4
[isabell@stardust ~]$

Step 4 - The Database Cluster

A database cluster is the base for all new single databases. We will define the location for the cluster and the user password. The user name for the cluster is automatically predefined to be your Uberspace name.

To reduce the effort for the database cluster administration, we will define at first the password and save it to the file .pgpass.

Create a ~/.pgpass file with the following content:

Warning

Replace <username> with your Uberspace name!

Warning

Replace the dummy password with your own!

#hostname:port:database:username:password (min 64 characters)
*:*:*:<username>:1234567890123456789012345678901234567890123456789012345678901234

In our example this would be:

#hostname:port:database:username:password (min 64 characters)
*:*:*:isabell:1234567890123456789012345678901234567890123456789012345678901234

And change the permissions with:

[isabell@stardust ~]$ chmod 0600 ~/.pgpass
[isabell@stardust ~]$

To use the pure password for the database cluster creation, create a temporary password file ~/pgpass.temp, containing only your password.

In our example this would be:

1234567890123456789012345678901234567890123456789012345678901234

Now create the database cluster:

[isabell@stardust ~]$ initdb --pwfile ~/pgpass.temp --auth=scram-sha-256 -E UTF8 -D ~/opt/postgresql/data/
The files belonging to this database system will be owned by user "<username>".
This user must also own the server process.

The database cluster will be initialized with locale "de_DE.UTF-8".
The default text search configuration will be set to "german".

Data page checksums are disabled.

creating directory /home/<username>/opt/postgresql/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 ... Europe/Berlin
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

   pg_ctl -D /home/<username>/opt/postgresql/data/ -l logfile start

[isabell@stardust ~]$

The temporary password file is no longer necessary:

[isabell@stardust ~]$ rm ~/pgpass.temp
[isabell@stardust ~]$

Configuration

After the installation of PostgreSQL, it is necessary to configure the network environment. This installation considers the loopback interface as well as access via an Unix socket. Access via an Unix socket is not supported by every project.

Configure the Unix Socket

The Unix socket will be configured to the standard port. You must set the environment variables with your new port:

Edit your ~/.bashrc and add the following content:

export PGHOST=localhost
export PGPORT=5432

Load the new settings:

[isabell@stardust ~] source ~/.bashrc

PostgreSQL Configuration

Edit ~/opt/postgresql/data/postgresql.conf and set the key values listen_adresses, port and unix_socket_directories. Consider using only unix sockets if possible.

Warning

Please replace <username> with your username!

Warning

If you set listen_addresses you might open your postgres installation to the world!

#------------------------------------------------------------------------------
# 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)
max_connections = 100                  # (change requires restart)
#superuser_reserved_connections = 3    # (change requires restart)
unix_socket_directories = '/home/<username>/tmp'      # comma-separated list of directories
                                       # (change requires restart)
#unix_socket_group = ''                # (change requires restart)
#unix_socket_permissions = 0777        # begin with 0 to use octal notation
                                       # (change requires restart)
#bonjour = off                         # advertise server via Bonjour
                                       # (change requires restart)
#bonjour_name = ''                     # defaults to the computer name
                                       # (change requires restart)

You can see the socket in the filesystem by using ls -a ~/tmp. It is listed as .s.PGSQL.5432.

Setup Daemon

Create ~/etc/services.d/postgresql.ini with the following content:

[program:postgresql]
command=%(ENV_HOME)s/opt/postgresql/bin/postgres -D %(ENV_HOME)s/opt/postgresql/data/
autostart=yes
autorestart=yes

After creating the configuration, tell supervisord to refresh its configuration and start the service:

[isabell@stardust ~]$ supervisorctl reread
SERVICE: available
[isabell@stardust ~]$ supervisorctl update
SERVICE: added process group
[isabell@stardust ~]$ supervisorctl status
SERVICE                            RUNNING   pid 26020, uptime 0:03:14
[isabell@stardust ~]$

To stop and start the daemon to perform maintenance tasks, you can use supervisorctl stop and supervisorctl start, respectively:

[isabell@stardust ~]$ supervisorctl stop postgresql
postgresql: stopped
[isabell@stardust ~]$

[isabell@stardust ~]$ supervisorctl start postgresql
postgresql: started
[isabell@stardust ~]$

Check out the supervisord manual for further details.

Database and User Management

It is highly recommended to use a separate user together with a strong password for every single usage (project). Please don’t use the database cluster user, it is like a root user.

The following example considers a database and new user for Synapse, the Matrix (https://matrix.org) reference server. You can use this example for other projects as well.

Note

Please start your PostgreSQL daemon before you maintain anything.

Step 1 - New User

To create a new database user, consider the following option:

  • -P: To get a user name and password dialogue.

Warning

Please replace <username> with your user name!

[isabell@stardust ~]$ createuser <username> -P
Enter password for new role:
Enter it again:
[isabell@stardust ~]$

Step 2 - New Database

The following options will be used to create the new database:

  • --encoding: Set of UTF8 encoding

  • --owner: The owner of the new database. In this example the new user of step 1.

  • --template: PostgreSQL supports standard templates to create the database structure.

  • database name: And as last option the name of the database. In this example ‘synapse’.

Warning

Please replace <username> with your user name, created in step 1!

[isabell@stardust ~]$ createdb --encoding=UTF8 --owner=<username> --template=template0 synapse
[isabell@stardust ~]$

Best Practices

To configure your project with the PostgreSQL details, you should have the database name, user name and password, localhost as server address and your port number.

Tested with Uberspace 7.1.15 and PostgreSQL 12.4

Written by: FM <git.fm@mmw9.de>