PostgreSQL Installation and service management

Windows installation process

Download the binary installation package from here and follow the prompts step by step.

Service Management

The name of the service can be used first toservices.mscview

λ net start postgresql-x64-10
postgresql-x64-10-PostgreSQL Server 10 service is starting.
postgresql-x64-10-The PostgreSQL Server 10 service has started successfully.

λ net stop postgresql-x64-10
postgresql-x64-10-PostgreSQL Server 10 service is stopping.
postgresql-x64-10-The PostgreSQL Server 10 service has stopped successfully.

Ubuntu installation Process

$ sudo apt-get install postgresql postgresql-contrib -y

Configure PostgreSQL Users

PostgreSQL uses user authentication and authorization similar to the UNIX permissions role. By default, PostgreSQL creates a new user named "Postgres" for Basic authentication. To use PostgreSQL, you need to log in to the "Postgres" account, which you can type by typing:

$ sudo su
$ su - postgres
[~] sudo su
Welcome to fish, the friendly interactive shell
Type help for instructions on how to use fish
?
[email protected] /h/whoami# su - postgres
[email protected]:~$

You can now access the PostgreSQL prompt with a command:

[email protected]:~$ psql
psql (9.5.11)
Type "help" for help.

postgres=#

Then change the password for the Postgres role:

postgres=# \password
Enter new password: 
Enter it again:

Service Management

• Activate the service for easy management

[~] sudo systemctl enable postgresql
Synchronizing state of postgresql.service with SysV init with /lib/systemd/systemd-sysv-install...
Executing /lib/systemd/systemd-sysv-install enable postgresql

• Start the service

[~] sudo systemctl start postgresql
%
[~] ps -ef | grep postgresql
postgres 10105     1  0 05:26 ?        00:00:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
whoami   10187  5712  0 05:26 pts/8    00:00:00 grep --color=auto postgresql

• Close Service

[~] sudo systemctl stop postgresql
%
[~] ps -ef | grep postgresql
whoami   10032  5712  0 05:25 pts/8    00:00:00 grep --color=auto postgresql

Local Certifications

New Server error Peer authentication failed for user "Postgres"

Modify the/etc/postgresql/9.5/main/pg_hba.conffollowing

Locate the following line:

local   all             postgres                                peer

Change into

local   all             postgres                                md5

Restart the service.

Remote access

/etc/postgresql/9.5/main/pg_hba.confthe modified IPv4 bind is as follows

/etc/postgresql/9.5/main/postgresql.confthe modified listen_addresses are as follows

Restart the service. Test as follows

λ psql -h 192.168.56.101 -p 5432 -U postgres -W
User postgres password:
psql (10.2, server 9.5.11)
SSL connection (protocol: TLSv1.2, password: ECDHE-RSA-AES256-GCM-SHA384, key bit: 256, compression: off)
Enter "help" for help.
                                                                
postgres = # 

PostgreSQL Installation and service management