Installing PostgreSQL 13

BCM recommends installing PostgreSQL version 13 which faster than the older versions. PostgreSQL 13 can be downloaded at https://www.postgresql.org/download/. BMC recommends installing PostgreSQL 13 on Linux. Some distributions such as Red-Hat Family, Debian, Ubuntu, Suze and OpenSuze are available. 

The Red Hat family of distributions includes:

• Red Hat Enterprise Linux
• CentOS
• Fedora
• Scientific Linux
• Oracle Linux

To install PostgreSQL on RedHat Enterprise Linux 7

1. Go to https://www.postgresql.org/download/linux/redhat/ and enter the following values: 

   1. Select 13 version.
   2. Select RedHat Enterprise Linux 7 platform.
   3. Select X86_64 architecture.

2. Install the repository RPM.

   sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

3. Install PostgreSQL.

   sudo yum install -y postgresql13-server

4. Initialize the database and enable automatic start.

   sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
   sudo systemctl enable postgresql-13
   sudo systemctl start postgresql-13

To configure the PostgreSQL database

1. Go to PGTune and provide basic information about the hardware configuration.

   

2. Select 13 from the DB version list.
3. Select Linux/OS X from the OS Type list.
4. Select Mixed type of applications from the DB Type list.
5. For Total Memory (RAM) and Number of CPUs see Hardware requirements.
   For the recommendations, see PostgreSQL recommendations.
6. Select 100 from the Number of Connections list.
7. Select type of hard drive which PostgreSQL use as storage from the Data Storage list.

8. Click Generate and go to the ALTER SYSTEM tab. The configuration for PostgreSQL is calculated based on the provided hardware configuration.

   

9. Click Copy configuration.

10. Connect to the PostgreSQL database.

    sudo -u postgresql psql
    postgres=#
    paste the copy configuration
    
    ALTER SYSTEM SET max_connections = '100';
    ALTER SYSTEM SET shared_buffers = '4GB';
    ALTER SYSTEM SET effective_cache_size = '12GB';
    ALTER SYSTEM SET maintenance_work_mem = '1GB';
    ALTER SYSTEM SET checkpoint_completion_target = '0.9';
    ALTER SYSTEM SET wal_buffers = '16MB';
    ALTER SYSTEM SET default_statistics_target = '100';
    ALTER SYSTEM SET random_page_cost = '4';
    ALTER SYSTEM SET effective_io_concurrency = '2';
    ALTER SYSTEM SET work_mem = '5242kB';
    ALTER SYSTEM SET min_wal_size = '1GB';
    ALTER SYSTEM SET max_wal_size = '4GB';
    ALTER SYSTEM SET max_worker_processes = '8';
    ALTER SYSTEM SET max_parallel_workers_per_gather = '4';
    ALTER SYSTEM SET max_parallel_workers = '8';
    ALTER SYSTEM SET max_parallel_maintenance_workers = '4';

11. Add the following settings to perform autovacuum.

    ALTER SYSTEM SET bgwriter_lru_maxpages = '1000';
    ALTER SYSTEM SET bgwriter_lru_multiplier = '4.0';
    ALTER SYSTEM SET random_page_cost = '2.0';
    ALTER SYSTEM SET cpu_tuple_cost = '0.03';
    ALTER SYSTEM SET log_autovacuum_min_duration = '0';
    ALTER SYSTEM SET autovacuum_max_workers = '5';
    ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms'; 
12. Enter # \q to quit the PostgreSQL database.

13. Restart the database.

    sudo systemctl restart postgresql-13

14. To check whether these settings are running, execute the following command.

    sudo -u postgresql psql
    postgres=# show shared_buffers;
    shared_buffers
    ----------------
    4GB

    The value of ‘shared_buffer’ varies depending on the configuration copied in step 9.