PostgreSQL 10 installation on linux mint 18.3 based on ubuntu 16.04 along with common troubleshooting

postgresql.sh

# *POSRTGRESQL INSTALLATION*
sudo apt-get install postgresql-10 postgresql-client-10 postgresql-server-dev-10 postgresql-doc-10 postgresql-contrib

configuration location:
/etc/postgresql/9.1/main/postgresql.conf
/etc/postgresql/10/

service location:
/etc/init.d/postgresql

data location:
/var/lib/postgresql/

Unix domain socket location:
/var/run/postgresql/

How to install TIMESCALEDB Extension

Add PPA
sudo add-apt-repository ppa:timescale/timescaledb-ppa

update cache
sudo apt-get update

install
sudo apt-get install timescaledb-postgresql-10

How to Change Passwordless psql into secure psql

Create password for postgres Unix user
$ sudo passwd postgres
Enter new UNIX password: <p455w0rd>
Retype new UNIX password: <p455w0rd>

Switch Unix user to postgres
$ su - postgres

Enter into psql and create password
$ psql
postgres=# ALTER USER postgres PASSWORD '<pass123>';
postgres=# SHOW config_file;
/etc/postgresql/10/main/postgresql.conf
postgres=# \q

edit configuration file
$ sudo nano /etc/postgresql/10/main/postgresql.conf
add the following line if not exists:
local all postgres md5
delete this line if exists:
local all postgres peer

restart
$ sudo /etc/init.d/postgresql restart

try calling psql with -U flag and postgres username
$ psql -U postgres
Password for user postgres:
<pass123>

postgres=#

congratulation! you succeeded

Common Misconception of User Name: postgres

• postgres as a unix username has a password <p455w0rd>;
• postgres as a postgresql username has a password <pass123>;
• they have the same user name: postgres ;
• they can have different passwords;
• two different environment: operating system and database server.

How to Avoid the Error

Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Clean removal of postgres
$ sudo apt-get remove --purge postgres*

delete all configuration files
$ sudo rm -rf /etc/postgresql/

reinstall postgresql
$sudo apt-get install postgresql-10