PostgreSQL on a Linux VM

You will need to install Ubuntu on your virtual host, I used VMware Workstation Player and ubuntu-17.10.1-server-amd64.iso

I called my instance ‘Ubuntu PSQL’ and used carl/carl username and password for authentication. Once the VM is running you can install postgresql:

1
2
3
4
5
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql postgresql-contrib
sudo apt-get install openssh-server
ip addr show

You can then putty to the server, this is easier as you can paste commands.

To check the PostgreSQL server is running you can query it with psql –version

All of my examples below were on version 9.6

1
2
3
4
5
6
7
8
9
10
11
12
#Check the PSQL version
psql --version

#Check status
service --status-all
sudo pg_lsclusters

#Other service commands
sudo service postgresql status
sudo service postgresql restart
sudo service postgresql stop
sudo service postgresql start

Configuration updates

The following configuration updates will be needed, I used nano to edit the files but you can use any other editor such as vi

pg_hba.conf

Set the METHOD to ‘trust’ under ‘# Database administrative login by Unix domain socket’

1
2
3
4
sudo nano /etc/postgresql/9.6/main/pg_hba.conf

# Database administrative login by Unix domain socket
local all postgres trust

You can also restrict access by IP or IP range:

Example:

1
2
192.168.0.254/32 = ip address of 192.168.0.254
192.168.0.1/24 = range of ip's from 192.168.0.1 to 192.168.0.1.255

Also see Subnet mask

Allow all connections:

1
2
3
4
5
-- allow the lot
host all all all md5

-- allow by username
host all USERNAME 0.0.0.0/0 md5

postgresql.conf

Set the the following:

1
2
3
4
5
6
sudo nano /etc/postgresql/9.6/main/postgresql.conf

listen_addresses = '*'
log_statement = 'all' 
log_hostname = on
log_line_prefix = '%m [%p] [%r] %q%u@%d '

Update password

You can update the root password

1
2
3
4
5
6
7
8
9
10
#set password
sudo -u postgres psql postgres
\password postgres
[newpassword]
\q
#test user/pass 
(Requires pg_hba.conf change to trust)

psql -U postgres -W
[newpassword]

Tail logs

1
2
tail -f /var/log/postgresql/postgresql-9.6-main.log
CRTL C ~ to quit

Backup & Restore

See putty if you need to copy a dump file to the server from your windows PC.

1
2
3
4
5
6
7
8
//Create the dump file
sudo -Su postgres PGCLUSTER="9.6/main" pg_dump -v -Fc productiondbname -f /tmp/production20180503.dump

//Create a new database
sudo -u postgres psql -c "CREATE DATABASE production20180503 OWNER = postgres TABLESPACE pg_default;"

//Restore
sudo -u postgres pg_restore --dbname "production20180503" --verbose /tmp/production20180503.dump

References