Install postgres 12.9 centos 7

2 months ago

How to install postgres 12.9 on CentOS 7 along with change password, check version, setup remote connect and handle common error during the process.

Go to https://www.postgresql.org/download/linux/redhat/

Choose your version 

Copy and run

# 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

# Install PostgreSQL:
sudo yum install -y postgresql12-server

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

Check postgres install

psql -V
psql (PostgreSQL) 12.9

Change password

Enter your password.

Login to postgress.

sudo -i -u postgres
psql
psql (12.9)

#or
su - postgres

Remote connect to postgres

su - postgres
password:

-bash-4.2$ psql -U postgres -c 'SHOW config_file'
---------------------------------------
 /var/lib/pgsql/12/data/postgresql.conf

Exit the postgres

exit

Update the file above

sudo nano /var/lib/pgsql/12/data/postgresql.conf

# remove the comment and add your server IP
listen_addresses = '*'            # what IP address(es) to listen on;

Restart postgres server

# get postgres service
systemctl list-units|grep postgresql
  postgresql-12.service                                                                                 loaded active     running          PostgreSQL 12 database server

# restart
sudo service postgresql-12.service restart

Check postgres database before connec

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sales     | ubuntu   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
psql -h <ip-address> -p 5432 -U postgres postgres
psql: error: FATAL:  no pg_hba.conf entry for host 

Fix that by:

# get file to edit
postgres=# SHOW hba_file;
              hba_file              
------------------------------------
 /var/lib/pgsql/12/data/pg_hba.conf
(1 row)

# edit file
# Add the following line as the first line of pg_hba.conf It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD (I use tab instead of space)
host    all     all     0.0.0.0/0	md5

Restart postgres

If your get password error.

Fix it by change the password

sudo su - postgres
postgres=# \password

Now try again

 psql -h 45.76.*** -p 5432 -U postgres postgres
Password for user postgres: 
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.