Postgres: Setup

(Last Updated On: )

If you want to setup a database server for Postgresql 9.6 these are the basic steps that should be done when working with Ubuntu 16.04.

Installation:

sudo apt-get update
sudo apt-get upgrade
sudo reboot (if necessary)
sudo add-apt-repository “deb https://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main”
tee -a /etc/apt/sources.list.d/pgdg.list
wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
apt-get upgrade
sudo apt-get install postgresql-9.6
cd /var/
mkdir pg_log (This is where the logs will be stored)
chmod o+wxr /var/pg_log/
cd /etc/postgresql/9.6/main
nano postgresql.conf:
- CONNECTIONS AND AUTHENTICATION
  - set “listen_addresses” = ‘*’
  - Set “max_connections” to 250 (or a reasonable number that works for your setup)
  - logging_collector = on
  - log_directory = ‘/var/pg_log’
  - log_file_mode = 0506
- AUTOVACUUM PARAMETERS
  - autovacuum = on
    - Remove #
  - autocavuum_analyze_threshold = 100
  - autovacuum_vacuum_threshold = 100
  - track_counts = on
nano pg_hba.conf
- host all all 0.0.0.0/0 md5
- local all postgres trust
systemctl enable postgresql
systemctl start postgresql
/etc/init.d/postgresql restart
-u postgres psql template1
ALTER USER postgres with encrypted password ‘SET A PASSWORD’;
\q
-u postgres psql
CREATE EXTENSION adminpack;
- To ensure it was installed correctly run “select * from pg_extension;”
If you want to view the postgresql service to find what directories are currently being using then run “ps auxw | grep postgres | grep — -D”

-u postgres psql
CREATE DATABASE ##DATABASE NAME##;
- First run \l to check that DB doesn’t exist already.
CREATE ROLE ##ROLE NAME## LOGIN ENCRYPTED PASSWORD ‘##PASSWORD##’;
- You can specify certain options like CREATEDB, NOSUPERUSER, CREATEROLE, INHERIT, etc. See documentation for more. https://www.postgresql.org/docs/9.6/static/user-manag.html
GRANT ##REQUIRED## PRIVILEGES ON DATABASE ##DATABASE NAME##TO ##ROLE NAME##;