- While logged in as the root user, initialize the database folder according to this variable “/var/lib/pgsql/10/data.” by using the command:
sudo -u postgres /usr/pgsql-10/bin/initdb -D /var/lib/pgsql/10/data
The database cluster data is stored in a cluster’s data directory, configured as an environment variable called “$PGDATA.”
The configuration file for postgresql should be edited for the correct running of Postgres.
- Open the file using Vim editor as shown below by using the command:
vi /var/lib/pgsql/10/data/postgresql.conf
- Make the following changes to the file:
| Configuration | Description |
|---|---|
| port = 5432 | Enables the listening port. |
| wal_level = replica | Logs enough metadata to the logs to enable streaming replication. |
| wal_log_hints = on | Enables the PG_REWIND option for the NODE REJOIN. |
| archive_mode = on | Starts the archive mode to handle full WAL segments. WAL prevents data loss in case of circumstances like operating system crash, hardware failure, or PostgreSQL crash. |
| archive_command = ‘/bin/true’ | Determines the command to perform on full WAL segments. Explore backup and recovery options. If you decide to change this command later on, you will need to change it live without any restart. |
| max_wal_senders = 10 | Determines the number of WAL senders for secondary nodes— recommended amount is 10 senders (Required by REPMGR) |
| wal_sender_timeout = 3600s | Terminate replication connections that are inactive longer than the specified number of milliseconds. This is useful for the sending server to detect a standby crash or network outage |
| max_replication_slots = 10 | Determines how many replication slots should be allowed— recommended amount is 10 slots (Required by REPMGR) |
| hot_standby = on | Permits the server to act as a hot standby (Should it be a replica) |
| autovacuum = on | Starts the autovacuum daemon in PostgreSQL— this must remain on. The thresholds for the vacuum and analysis should be set according to the applicative data structure. Autovacuum checks for tables that have had a large number of inserted, updated, or deleted tuples. |
| work_mem = 4MB | This sets the private memory area for sort and hashing. |
| shared_buffers = 2GB | This sets the shared buffer memory area for all sessions/users. |
| max_connections = 1000 | Determines how many connections PostgreSQL should accept |
| listen_addresses = ‘*’ | Allows PostgreSQL to listen/bind to all incoming connections from any source IP/hostname. |
| shared_preload_libraries = ‘repmgr’ | Preloads the repmgr executables. Required by REPMGR. |
- Save and exit by typing
:wqand then press Enter.
- Next, configure the “/var/lib/pgsql/10/data/pg_hba.conf” file. This is a host-based authentication (HBA) configuration file. Add rules to ensure that it accepts all connections from all sources.
- Open the file using the following command:
vi /var/lib/pgsql/10/data/pg_hba.conf - After the configuration file is open, add the following under the “#replication privilege” comment. The entry should look like this:
- Open the file using the following command:
| host | all | all | {define the subnet x.x.x.0/24} | md5 |
| host | replication | all | {define the subnet x.x.x.0/24} | md5 |
For Example:
| host | all | all | 172.30.202.0/24 | md5 |
| host | replication | all | 172.30.202.0/24 | md5 |
- Save and exit by typing
:wqand then press Enter.
- Next, you need to add “postgres” as a sudoer for the purpose of enabling the “system_start_command” in repmgr.
- Make sure you are logged in as a “root” user and create a file called “/etc/sudoers.d/postgres. Enter the command:
vi /etc/sudoers.d/postgres - Add the following content to the file:
Defaults:postgres !requiretty
postgres ALL = NOPASSWD: /usr/bin/systemctl stop postgresql-10,/usr/bin/systemctl start postgresql-10,/usr/bin/systemctl restart postgresql-10,/usr/bin/systemctl reload postgresql-10![]()
- Save and exit by typing
:wqand then press Enter.
- Make sure you are logged in as a “root” user and create a file called “/etc/sudoers.d/postgres. Enter the command:
- After you complete these changes and configurations, restart postgresql while logged as the “root” user by using the command:
systemctl restart postgresql-10
- Once postgresql restarts, you need to open the default port (5432) by using firewall-cmd:
firewall-cmd --add-port=5432/tcp --permanentfirewall-cmd --reload
Once completed, repeat the whole process for the secondary node.
Need more help with this?
Click here to open a Support ticket