Ubuntu 16.04.1 LTS PostgreSQL installation configuration

PostgreSQL installation
--------------------
Binary installation:
wget https://get.enterprisedb.com/postgresql/postgresql-9.5.6-1-linux-x64-binaries.tar.gz
Tar XF postgresql-9.5.6-1-linux-x64-binaries.tar.gz-c/usr/local/
Useradd Postgres
Mkdir-p/data/postgresql/{data,log}
Chown-r postgres.postgres/data/postgresql/

Initialize the database:
Su Postgres
Cd/usr/local/pgsql/bin
./initdb-e utf8-d/data/postgresql/data/

Startup script:
Vi/etc/systemd/system/postgresql.service
[Unit]
Description=postgresql Database Server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
oomscoreadjust=-1000
Environment=pg_oom_adjust_file=/proc/self/oom_score_adj
Environment=pg_oom_adjust_value=0
environment=pgstarttimeout=270
Environment=pgdata=/data/postgresql/data
Execstart=/usr/local/pgsql/bin/pg_ctl start-d ${pgdata}-s-w-T ${pgstarttimeout}
Execstop=/usr/local/pgsql/bin/pg_ctl stop-d ${pgdata}-s-m Fast
Execreload=/usr/local/pgsql/bin/pg_ctl reload-d ${pgdata}-S
timeoutsec=300

[Install]
Wantedby=multi-user.target

Start off and boot up:
Systemctl Daemon-reload
Systemctl Enable PostgreSQL
Systemctl start PostgreSQL
Systemctl Stop PostgreSQL
Systemctl Restart PostgreSQL

PostgreSQL configuration
-----------------------
Configuration file: Default in the Data directory

Master configuration file:/data/postgresql/data/postgresql.conf
Allow remote access-listen_addresses = ' localhost,192.168.30.3 '

Permissions control File:/data/postgresql/data/pg_hba.conf
# "Local" is for Unix domain sockets connections only
Local all MD5
# IPV4 Local connections:
Host All 192.168.30.2/32 MD5
# IPV6 Local connections:
Host all:: 1/128 MD5

Reboot makes the configuration file effective: Systemctl Reload PostgreSQL

Note: If you have not set the password for the Postgres user, you need to set the local to trust first:
Local all All trust
Then set the password, and then change it back
Psql-u Postgres
postgres-# ALTER USER postgres PASSWORD ' xxxx ';

Create databases and authorizations
----------------------
Create users: postgres=# Create user fishing password ' xxxx '
Create database fishing owner fishing;

Exit the database and log in to the new database with the new user: Psql-u fishing-d Fishing
To test database permissions:
1. Create a table
CREATE TABLE Test (
ID int PRIMARY key NOT NULL,
Name text NOT NULL
);

2. View the table
Fishing-> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
Public | Test | Table | Fishing
(1 row)

3. Inserting data into the table
INSERT into Test (Id,name) VALUES (1, ' AA ');
SELECT * from Test;

4. Deleting a test table
drop table test;

Ubuntu 16.04.1 LTS PostgreSQL installation configuration