Skip to Content

PostgreSQL installation on Linux - with database creation

PostgreSQL configuration cheat sheet!

Share on:
postgresql installation database creation

PostgreSQL is open source software so we can install it wthout any limitation, including our personal code changes. It is distributed under own The PostgreSQL License(TPL).

Installation of PostgreSQL is easy on Linux Distributions.
For purpose of tutorial we use CentOS 8.

We shuold get newest PostgreSQL from official repo - version in OS repos are mostly old - like for now when we get with CentOS 8 PostgreSQL 9.6 :)

Install PostgreSQL RPM repo


Newest links for repos: PostgreSQL Official Repos


[root@postgres-lab ~]## dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 0:27:00 ago on Sun 28 Jun 2020 09:01:23 PM UTC.
pgdg-redhat-repo-latest.noarch.rpm                                                                                             15 kB/s |  11 kB     00:00    
Dependencies resolved.
==============================================================================================================================================================
 Package                                    Architecture                     Version                             Repository                              Size
==============================================================================================================================================================
Installing:
 pgdg-redhat-repo                           noarch                           42.0-11                             @commandline                            11 k

Transaction Summary
==============================================================================================================================================================
Install  1 Package

Total size: 11 k
Installed size: 11 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                      1/1
  Installing       : pgdg-redhat-repo-42.0-11.noarch                                                                                                      1/1
  Verifying        : pgdg-redhat-repo-42.0-11.noarch                                                                                                      1/1

Installed:
  pgdg-redhat-repo-42.0-11.noarch                                                                                                                             

Complete!
[root@postgres-lab ~]##

Disable PostgreSQL OS module

As mentioned earlier CentOS 8 has got build in module for PostgreSQL easy install. Despite that version of RDBMS is too old so we will disable it.

[root@postgres-lab ~]## dnf -qy module disable postgresql

Install PostgreSQL 12 with client tools

[root@postgres-lab ~]## dnf install postgresql12-server postgresql12
Last metadata expiration check: 0:02:58 ago on Sun 28 Jun 2020 09:30:47 PM UTC.
Dependencies resolved.
==============================================================================================================================================================
 Package                                      Architecture                    Version                                   Repository                       Size
==============================================================================================================================================================
Installing:
 postgresql12                                 x86_64                          12.3-5PGDG.rhel8                          pgdg12                          1.6 M
 postgresql12-server                          x86_64                          12.3-5PGDG.rhel8                          pgdg12                          5.1 M
Installing dependencies:
 postgresql12-libs                            x86_64                          12.3-5PGDG.rhel8                          pgdg12                          395 k

Transaction Summary
==============================================================================================================================================================
Install  3 Packages

Total download size: 7.1 M
Installed size: 30 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): postgresql12-libs-12.3-5PGDG.rhel8.x86_64.rpm                                                                          365 kB/s | 395 kB     00:01    
(2/3): postgresql12-12.3-5PGDG.rhel8.x86_64.rpm                                                                               1.2 MB/s | 1.6 MB     00:01    
(3/3): postgresql12-server-12.3-5PGDG.rhel8.x86_64.rpm                                                                        3.0 MB/s | 5.1 MB     00:01    
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                         4.1 MB/s | 7.1 MB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                      1/1
  Installing       : postgresql12-libs-12.3-5PGDG.rhel8.x86_64                                                                                            1/3
  Running scriptlet: postgresql12-libs-12.3-5PGDG.rhel8.x86_64                                                                                            1/3
  Installing       : postgresql12-12.3-5PGDG.rhel8.x86_64                                                                                                 2/3
  Running scriptlet: postgresql12-12.3-5PGDG.rhel8.x86_64                                                                                                 2/3
  Running scriptlet: postgresql12-server-12.3-5PGDG.rhel8.x86_64                                                                                          3/3
  Installing       : postgresql12-server-12.3-5PGDG.rhel8.x86_64                                                                                          3/3
  Running scriptlet: postgresql12-server-12.3-5PGDG.rhel8.x86_64                                                                                          3/3
  Verifying        : postgresql12-12.3-5PGDG.rhel8.x86_64                                                                                                 1/3
  Verifying        : postgresql12-libs-12.3-5PGDG.rhel8.x86_64                                                                                            2/3
  Verifying        : postgresql12-server-12.3-5PGDG.rhel8.x86_64                                                                                          3/3

Installed:
  postgresql12-12.3-5PGDG.rhel8.x86_64            postgresql12-server-12.3-5PGDG.rhel8.x86_64            postgresql12-libs-12.3-5PGDG.rhel8.x86_64           

Complete!
[root@postgres-lab ~]##

Initialize first PostgreSQL Cluster

We will create sample data cluster.
First we create directory for all PostgreSQL data:

[root@postgres-lab ~]## mkdir -p /postgresql/data
[root@postgres-lab ~]## chown postgres:postgres /postgresql -R

Now from postgres OS user we can create data cluster:

[postgres@postgres-lab ~]$ /usr/pgsql-12/bin/initdb -D /postgresql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /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 ... UTC
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
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. You can now start the database server using:

    /usr/pgsql-12/bin/pg_ctl -D /postgresql/data -l logfile start

Create systemd service

Firt we will enable PostgreSQL service:

[root@postgres-lab ~]## systemctl enable postgresql-12
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-12.service → /usr/lib/systemd/system/postgresql-12.service

Now we have to modify service to reflect our PostgreSQL directory:

[root@postgres-lab ~]## vi /usr/lib/systemd/system/postgresql-12.service
[...]
[Service]
Type=notify

User=postgres
Group=postgres


Environment=PGDATA=/postgresql/data

[...]

Reload daemon after service chnages:

[root@postgres-lab ~]## systemctl daemon-reload

Start PostgreSQL from service

[root@postgres-lab ~]## systemctl start postgresql-12
[root@postgres-lab ~]## systemctl status postgresql-12
● postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-06-28 21:46:29 UTC; 47s ago
     Docs: https://www.postgresql.org/docs/12/static/
  Process: 1972 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 1977 (postmaster)
    Tasks: 8 (limit: 3248)
   Memory: 22.8M
   CGroup: /system.slice/postgresql-12.service
           ├─1977 /usr/pgsql-12/bin/postmaster -D /postgresql/data
           ├─1979 postgres: logger   
           ├─1981 postgres: checkpointer   
           ├─1982 postgres: background writer   
           ├─1983 postgres: walwriter   
           ├─1984 postgres: autovacuum launcher   
           ├─1985 postgres: stats collector   
           └─1986 postgres: logical replication launcher   

Jun 28 21:46:29 postgres-lab systemd[1]: Starting PostgreSQL 12 database server...
Jun 28 21:46:29 postgres-lab postmaster[1977]: 2020-06-28 21:46:29.104 UTC [1977] LOG:  starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC>
Jun 28 21:46:29 postgres-lab postmaster[1977]: 2020-06-28 21:46:29.135 UTC [1977] LOG:  listening on IPv6 address "::1", port 5432
Jun 28 21:46:29 postgres-lab postmaster[1977]: 2020-06-28 21:46:29.136 UTC [1977] LOG:  listening on IPv4 address "127.0.0.1", port 5432
Jun 28 21:46:29 postgres-lab postmaster[1977]: 2020-06-28 21:46:29.138 UTC [1977] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jun 28 21:46:29 postgres-lab postmaster[1977]: 2020-06-28 21:46:29.141 UTC [1977] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Jun 28 21:46:29 postgres-lab postmaster[1977]: 2020-06-28 21:46:29.153 UTC [1977] LOG:  redirecting log output to logging collector process
Jun 28 21:46:29 postgres-lab postmaster[1977]: 2020-06-28 21:46:29.153 UTC [1977] HINT:  Future log output will appear in directory "log".
Jun 28 21:46:29 postgres-lab systemd[1]: Started PostgreSQL 12 database server.