Initial setup required from installation of PostgreSQL on CentOS to startup.

Publication date：December 17, 2020

summary

This is the initial configuration procedure required to install and start PostgreSQL on CentOS. This description assumes CentOS7. The section on security is also included.

PostgreSQL is recommended because it is free and has high functionality. I have heard that PostgreSQL functions used to be slow in the past, but the latest versions have been improved and are faster.

The versions are as follows

1. install

This section describes the procedure for installing PostgreSQL.

1-1. Installing PostgreSQL

Execute PostgreSQL installation by yum command. Please work as a root user.

[username@hostname ~]$ su -

[root@hostname ~]# yum -y install postgresql-server

2. Setting details

This section describes the setup before starting PostgreSQL.

2-1. Create a dedicated PostgreSQL user.

I wanted to create a dedicated user to operate PostgreSQL (can use psql commands), but the user was already created. I remember creating the user when I created it in the past, so maybe the specs have changed.

Try switching to a user with the following command.

[root@hostname ~]# su - postgres

As some of you may be using an older version of PostgreSQL, it should be noted that the following procedure to change ".bashrc" and set environment variables is no longer necessary.

export PGHOME=/var/lib/pgsql

export PGDATA=/var/lib/pgsql/data

export PGHOST=localhost

The default PGDATA for the postgres user seems to be now "/var/lib/pgsql/data". Refer to "/usr/lib/systemd/system/postgresql.service" for the description.

2-2. PostgreSQL Data Preparation

Create PostgreSQL data. The encoding is assumed to be UNICODE. The directory "/var/lib/pgsql/data" was created by default, but if you have not created it, please create a folder as well.

-bash-4.2$ initdb --encoding=UNICODE

Since "/usr/lib/systemd/system/postgresql.service" is set to "PGDATA=/var/lib/pgsql/data", "initdb --encoding=UNICODE" will create a DB under "/var/lib/pgsql/ data/", DB will be constructed under "/var/lib/pgsql/data/".

2-3. Modification of configuration files

Allow IP addresses to access PostgreSQL. For non-local access, configure the system to be password-authenticated.

[root@hostname ~]# vi /var/lib/pgsql/data/postgresql.conf

Modify "postgresql.conf" as follows

#------------------------------------------------------------------------------

# CONNECTIONS AND AUTHENTICATION

#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

#------------------------------------------------------------------------------

# CONNECTIONS AND AUTHENTICATION

#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'         # what IP address(es) to listen on;

To add to this as it is an important part of security, if you include this setting, access will be possible from any IP. If the IP to be accessed is fixed, please state the fixed IP.

This setting is required when logging in from a server other than the server where PostgreSQL is installed, such as pgadmin. This setting is not necessary when logging in to the server and executing psql commands. (since the connection originates from the server itself (localhost)).

This is an important security setting, so minimize the number of users who can access it.

Then modify "pg_hba.conf".

[root@hostname ~]# vi /var/lib/pgsql/data/pg_hba.conf

Please add the following information to the end of the document

#Password authentication from outside

host    all             all             0.0.0.0/0            md5

By including this setting, a password is required when connecting from the outside. If this setting is not set, or if it is set incorrectly, it will cause a serious security hole, so please set it with the utmost care.

2-4. Startup Confirmation

Now that the preconfiguration is complete, check to see if PostgreSQL starts. Since we built the DB as a postgres user, we switch to the postgres user and then start it.

[root@hostname ~]# su - postgres

-bash-4.2$ pg_ctl start

After PostgreSQL is successfully started, check that the psql command works without problems. Run the psql command as the postgres user.

-bash-4.2$ psql -l

                                         Database List

   Name    |  Owner   | Encoding         |  Collation Order   | Ctype(conversion operator) |      Access rights

-----------+----------+------------------+--------------------+----------------------------+-----------------------

 postgres  | postgres | UTF8             | ja_JP.UTF-8        | ja_JP.UTF-8                |

 template0 | postgres | UTF8             | ja_JP.UTF-8        | ja_JP.UTF-8                | =c/postgres          +

           |          |                  |                    |                            | postgres=CTc/postgres

 template1 | postgres | UTF8             | ja_JP.UTF-8        | ja_JP.UTF-8                | =c/postgres          +

           |          |                  |                    |                            | postgres=CTc/postgres

(3 lines)

If the above results are returned, it is OK.

2-5. Automatic startup setting

Although it is not required, it is hard to start PostgreSQL every time the server is rebooted, so we will configure PostgreSQL to start automatically when the server starts. Register the systemctl command as well. Please note that this procedure is for CentOS7, so those who are using other than CentOS7 need to use the Service command.

Check if "/usr/lib/systemd/system/postgresql.service" exists. If not, create "postgresql.service" as follows. Execute as root user.

[root@hostname ~]# touch /etc/systemd/system/postgresql.service

[root@hostname ~]# vi /etc/systemd/system/postgresql.service

The following information is provided

[Unit]

#Description.

Description=PostgreSQL

#Control before and after execution

#Before=xxx.service

After=network.target

[Service]

#User and group designation

User=postgres

Group=postgres

#Once activated, set the status to Activated.

Type=oneshot

RemainAfterExit=yes

#Start, stop, reload

Environment=PGDATA=/var/lib/pgsql/data

ExecStart=/usr/bin/pg_ctl start -D /var/lib/pgsql/data

ExecStop=/usr/bin/pg_ctl stop

ExecReload=/usr/bin/pg_ctl reload

[Install]

#runlevel3 equivalent setting

WantedBy=multi-user.target

Next, register it with the systemctl command.

[root@hostname ~]# systemctl enable postgresql

[root@hostname ~]# systemctl is-enabled postgresql

enabled

[root@hostname ~]# systemctl list-unit-files --type=service | grep postgresql

postgresql.service                                enabled

[root@hostname ~]# systemctl daemon-reload

2-6. Adding an Administrative User to PostgreSQL

Create a user to be used within the PostgreSQL application, separate from the user who operates PostgreSQL from Linux. This user will be the user used when operating PostgreSQL via pgadmin, etc. We want to change the password of the default postgres user.

Execute the following command as the postgres user

-bash-4.2$ psql

postgres=# alter role postgres with password 'password';

postgres=# \q

※Please enter the password you wish to set in the "password" field.

2-7. Drilling holes in firewalls

Permit access to the PostgreSQL port since the default value for CentOS7 is that the firewall (iptables for CentOS6 and earlier) only allows ssh access. Add the "permanent" option to make the configuration permanent.

[root@hostname ~]# firewall-cmd --permanent --zone=public --add-port=5432/tcp

[root@hostname ~]# firewall-cmd --reload

[root@hostname ~]# firewall-cmd --list-all

public (active)

  target: default

  icmp-block-inversion: no

  interfaces: eth0

  sources:

  services: dhcpv6-client http https ssh

  ports: 8080/tcp 5432/tcp

  protocols:

  masquerade: no

  forward-ports:

  source-ports:

  icmp-blocks:

  rich rules:

When "ports: 5432/tcp" is displayed, it is OK.

Try accessing the site with pgadmin. You should be able to log in as the postgres user with the password you just set.

3. summary

We have described the initial setup required when you install PostgreSQL. The initial setup of PostgreSQL can be a struggle, so please refer to this section.

Thank you for taking the time to read this to the end.