See this page for more details on how to install PostgreSQL:
https://www.postgresql.org/download/linux/redhat/

yum update yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-2.noarch.rpm yum list postgresql* yum install postgresql10 postgresql10-server postgresql10-contrib
Initialize PostgreSQL database: /usr/pgsql-10/bin/postgresql-10-setup initdb
Change postgres password: passwd postgres

mkdir /data/ /data/pgsql/ /data/pgsql/10/ mv /var/lib/pgsql/10/data /data/pgsql/10/ ln -s /data/pgsql/10/data /var/lib/pgsql/10/data chown -R postgres:postgres /var/lib/pgsql/10/data chown -R postgres:postgres /data/pgsql

systemctl enable postgresql-10 systemctl start postgresql-10
Check PostgreSQL version: $ psql -V psql (PostgreSQL) 10

Switch to postgres user: su - postgres
Change postgres database user password: psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'postgres';"

Switch to postgres user: su - postgres
Configure g_hba.conf: vi /var/lib/pgsql/10/data/pg_hba.conf # IPv4 local connections: #host all all 127.0.0.1/32 ident #host all all 192.168.2.33/10 trust host all all 0.0.0.0/0 md5
Note: md5 means that you need to provide a password to connect to PostgreSQL. Use trust to connect without providing a password.

Configure postgresql.conf: vi /var/lib/pgsql/10/data/postgresql.conf #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) listen_addresses ='*'
Restart PostgreSQL: systemctl restart postgresql
Test PostgreSQL connection: netstat -nlt telnet 192.168.2.33 5432 psql -h 192.168.2.33 -U postgres

You need to switch to postgres user otherwise you might get an error if your run psql command: $ psql psql: FATAL: role "mtitek" does not exist
Switch to postgres user: su - postgres
Run psql command: psql postgres=#
To exit from PostgreSQL command line utility psql: Type \q and then press ENTER to quit psql.
Use psql to connect to a remote host: psql -h localhost -p 5432 -U postgres password for user postgres:
Use psql to connect to a remote host (provide password in the command line): PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres
Use psql to connect to a remote host (provide password using the ~/.pgpass file): vi ~/.pgpass #hostname:port:database:username:password localhost:5432:dbtest:postgres:postgres
psql -h localhost -p 5432 -U postgres -d dbtest postgres=#
Use psql to run a query on a remote host: psql -h localhost -p 5432 -U postgres -d dbtest -c "select * from test"

Run psql command and execute the command CREATE ROLE to create a new role: $ psql postgres=# CREATE ROLE user1 WITH LOGIN PASSWORD 'user1'; c \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication | {} user1 | | {}
Execute the command ALTER ROLE to assign permission to a user: postgres=# ALTER ROLE user1 CREATEDB; ALTER ROLE postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication | {} user1 | Create DB
Execute the command drop user to delete a user: postgres=# drop user user1;

Run psql command and execute the command CREATE DATABASE to create a new database: postgres=# CREATE DATABASE user1db1 OWNER user1;
Run psql command and execute the command GRANT ALL to grant privileges on a database: postgres=# GRANT ALL ON DATABASE user1db1 TO user1; postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --------------------+-------------+----------+-------------+-------------+----------------------------- user1db1 | user1 | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =Tc/user1 + | | | | | user1=CTc/user1
Execute the command drop database to delete a database: postgres=# drop database user1db1;

Use pg_dump tool to backup a database: pg_dump -h localhost -p 5432 -U postgres -d user1db1 > user1db1.dump.sql
Use psql to restore a database: psql -h localhost -p 5432 -U postgres -d user1db2 < user1db1.dump.sql

See this page for more details about Error Reporting and Logging:
https://www.postgresql.org/docs/current/static/runtime-config-logging.html

Configure postgresql.conf: #------------------------------------------------------------------------------ # REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, depending on platform. # csvlog requires logging_collector to be on. log_destination = 'stderr' # This is used when logging to stderr: # Enable capturing of stderr and csvlog into log files. # Required to be on for csvlogs. # (change requires restart) logging_collector = on # These are only used if logging_collector is on: # directory where log files are written, can be absolute or relative to PGDATA log_directory = 'log' # log file name pattern, can include strftime() escapes log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # creation mode for log files, begin with 0 to use octal notation log_file_mode = 0600 # If on, an existing log file with the same name as the new log file will be truncated rather than appended to. # But such truncation only occurs on time-driven rotation, not on restarts or size-driven rotation. # Default is off, meaning append to existing files in all cases. log_truncate_on_rotation = on # Automatic rotation of logfiles will happen after that time. 0 disables. log_rotation_age = 1d # Automatic rotation of logfiles will happen after that much log output. 0 disables. log_rotation_size = 10MB

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
sudo apt upgrade sudo apt install postgresql-9.6
You have to configure the files "pg_hba.conf" and "postgresql.conf" as seen above.
These files can found by default in this location: /etc/postgresql/9.6/main

When installing/uninstalling different versions of PostgreSQL, you might get this error when you try to run PostgreSQL tools:
$ psql Error: PostgreSQL version 11 is not installed
If you already uninstalled PostgreSQL 11, you can try to purge the installation and that in some cases should be enough to fix the issue:
$ sudo apt purge postgresql-11 ... Purging configuration files for postgresql-11 (11.5-3.pgdg18.04+1) ... Dropping cluster main...
But let say the above didn't fix the issue or the system start complaining about a new version of PostgreSQL:
$ psql Error: PostgreSQL version 10 is not installed
Let's check PostgreSQL cluster:
$ sudo pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.6 main 5432 online postgres /var/lib/postgresql/9.6/main pg_log/postgresql-%Y-%m-%d_%H%M%S.csv 10 main 5432 down,binaries_missing <unknown> /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
Check if you still have these folders:
$ ls -1 /var/lib/postgresql/10 ls: cannot access '/var/lib/postgresql/10': No such file or directory
$ ls -1 /etc/postgresql/10/main/ conf.d environment pg_ctl.conf pg_hba.conf pg_ident.conf postgresql.conf start.conf
Let's delete any left folders from PostgreSQL 10 installation:
$ sudo rm -rf /etc/postgresql/10/
Check now pg_lsclusters:
$ sudo pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.6 main 5432 online postgres /var/lib/postgresql/9.6/main pg_log/postgresql-%Y-%m-%d_%H%M%S.csv
Run psql:
$ psql psql (9.6.18) Type "help" for help. postgres=#