Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
335 lines (212 sloc) 10.9 KB
.. author:: FM <git.fm@mmw9.de>
.. tag:: lang-c
.. tag:: database

PostgreSQL

.. tag_list::

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) 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 version 9.6.10. 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/v9.6.10/postgresql-9.6.10.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-9.6.10.tar.gz
[isabell@stardust ~]$

Step 2 - Source Code Configuration, Compiling and Installation

Configure and compile the source code with the commands configure and make and finally install it with make install.

Note

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

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/python2: Compiling with Python 2.x support. Alternatively you can choose /usr/bin/python3. Both are links to the actual supported Python versions.
  • --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.

Important

For future usage with projects like Miniflux2, ejabberd, Matrix etc. it is recommended to consider everything like docs and especially additional modules by PostgreSQL. This is the reason to use the supported option world for make and make install.

[isabell@stardust ~]$ cd ~/postgres/postgresql-9.6.10
[isabell@stardust ~]$ ./configure --prefix=$HOME/opt/postgresql/ --with-python PYTHON=/usr/bin/python2 --without-readline
[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) 9.6.10
[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!

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:

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 varables 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:

Warning

Replace replace <username> with your username!

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

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 :manual:`supervisord manual <daemons-supervisord>` 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!

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!

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 9.6.10

.. author_list::
You can’t perform that action at this time.