Helpful PostgreSQL Info

Many thanks to Sergey Pashinin and Christophe Pettus for their excellent videos here and here.

Install:

sudo apt-get install postgresql

Install Extras:

sudo apt-get install postgresql-contrib

Install Client (Command Line):

sudo apt-get install postgresql-client

Install GUI:

sudo apt-get install pgadmin3

Config Files:

cd /etc/postgresql/<<version>>/main/

Ports:

netstat -anp | grep postgre

Control:

service postgresql [force-reload, reload, restart, start, status, stop]

Shell Command Line:

sudo su postgres
cd ~
psql

List Databases:

postgres=# \l

List Users:

postgres=# \du

Quit shell:

postgres=# \q

Change root user password (From default):

postgres=# ALTER USER postgres WITH password 'ANY PASSWORD';

Create user:

postgres=# CREATE USER user1 WITH password 'ANY PASSWORD';

Change user role:

postgres=# ALTER USER user1 WITH SUPERUSER;

Delete user:

postgres=# DROP USER user1;

Change listen IP and config password encryption:

cd /etc/postgresql/<<version>>/main
sudo nano postgresql.conf

Uncomment “listen_addresses”, and fill in the IP of the server (by default PostgreSQL only listens on localhost loopback). Uncomment “password_encryption”.

Change permissions for client connections:

cd /etc/postgresql/<<version>>/main
sudo nano pg_hba.conf

Under “# IPv4 local connections” add this line…

host     all     all     <<Your Network exp. 192.168.0.0>>/<<Network Segment 16 or 24 or 32>>     md5

Config Memory, Checkpoints, and Planner Constants:

cd /etc/postgresql/<<version>>/main
sudo nano postgresql.conf
         ### Change:
shared_buffers = 1 GB # About 20% of total system memory
# Work Mem start low: 32-64MB... if system feels constrained then 
# look for temp files being created on disk to cover 
# work mem that goes above 64MB for queries.
work_mem = 64MB
# Maint work mem, used for things such as creating indexes
# may want to bump up if doing a lot of maintenance jobs on db creation
maintenance_work_mem = 1GB
# Always set wal buffers to 16 MB
wal_buffers = 16MB
# Set good values for checkpoints
checkpoint_segments = 32
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
# Give planner an idea of the impact of going to disk
# 3.0 for RAID 10, 2.0 for SAN, 1.1 for SSD
random_page_cost = 1.1
# Give the execution plans an idea of how much
# cache (RAM) is on the system
effective_cache_size = 2GB

Change Database Directory:

sudo su postgres
psql
postgres=# SHOW data_directory;
postgres=# \q

sudo service postgresql stop
cd /etc/postgresql/<<version>>/main
sudo nano postgresql.conf
          ### Change data_directory = '/usr/data/DB/main'
sudo mv /var/lib/postgresql/<<version>>/main /usr/data/DB
sudo service postgresql start

Config logging:

cd /etc/postgresql/<<version>>/main
sudo nano postgresql.conf
          ### Append to file
log_destination = 'csvlog'
log_directory = 'pg_log'
logging_collector = on
log_filename = 'postgres-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 250ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_tmp_files = 0

Config data checksums:

cd /etc/postgresql-common
sudo nano sudo nano createcluster.conf
          ### Change
# Options to pass to initdb, checksums setting must be done before db creation.
initdb_options = '--data-checksums'

Restart to make changes take affect:

service postgresql restart

Another great resource: https://help.ubuntu.com/community/PostgreSQL

pynut.com