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

Choose your version 

Copy and run

# Install the repository RPM:
sudo yum install -y

# 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 (12.9)

su - postgres

Remote connect to postgres

su - postgres

-bash-4.2$ psql -U postgres -c 'SHOW config_file'

Exit the postgres


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;
(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	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.