How to Initialize PostgreSQL Database in Linux

By Posted on

Introduction

This article is focusing on how to initialize PostgreSQL database. The execution is in Linux CentOS 8. As an example, it is using virtual machine to be able to perform it. This is a continuation of the previous article. Actually, the previous article exist in this link with the title of ‘How to Install PostgreSQL Database Server using compressed file in CentOS 8’. So, after finishing on installing PostgreSQL Database, before activating it, initialize it first. There is an existing PostgreSQL database exist in ‘/opt/postgresql-12.0/app’. Before using it, initialize the PostgreSQL database first.

Initialize PostgreSQL database

Without further explanation, the following are steps for initializing PostgreSQL database :

  1. First of all, create a new user for managing the administration of PostgreSQL database server. Choose ‘pgsql’ as the user name. The following is the execution for creating the user : 
    [root@10 ~]# adduser pgsql
[root@10 ~]#
  2. In order to create a clean installation separating the application and the data, create a new folder specifically to store the data. For an example, the location exist in ‘/opt/postgresql-12.0/data’. Compare it with the PostgreSQL application which exist in ‘/opt/postgresql-12.0/app’. For creating the folder, execute the following command : 
    [root@10 ~]# mkdir /opt/postgresql-12.0/data
[root@10 ~]# chown pgsql.pgsql /opt/postgresql-12.0/data/
[root@10 ~]# chmod 700 /opt/postgresql-12.0/data/
[root@10 ~]#
  3. Next, do not forget to add the path of the PostgreSQL application into the environment variable definition of the ‘pgsql’ user. It exist in the file of ‘.bashrc’ inside the home user path. Just execute the command as follows : 
    [root@10 ~]# echo "PATH=$PATH:/opt/postgresql-12.0/app/bin" >> /home/pgsql/.bashrc
[root@10 ~]#
  4. Switch to user ‘pgsql’ to begin initialize the data for the PostgreSQL database by executing the following command : 
    [root@10 ~]# su - pgsql
[pgsql@10 ~]$
  5. Initialize the data with the following command execution : 
    [pgsql@10 ~]$ initdb -D /opt/postgresql-12.0/data
The files belonging to this database system will be owned by user "pgsql".
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 /opt/postgresql-12.0/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 ... America/New_York
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:

    pg_ctl -D /opt/postgresql-12.0/data -l logfile start

[pgsql@10 ~]$

As in the above output in the last step, the initialization process is a success. So, to start the PostgreSQL, just execute the command : 

pg_ctl -D /opt/postgresql-12.0/data -l logfile start

Leave a Reply