PostgreSQL
2017-01, rickatech

For a long time two open source databases have remained prominant: MySQL and PostgreSQL.  PostgreSQL has offered a more compelling enterprise database feature set compared to other open source database offerings.

Products, Common Terms

Role - a named identity that can perform certain functions, but typically can not login remotely.  See also User.

User - technically a user is just a role that can login.  See also Role.

...

RHEL/CentOS 7 Setup

The standard yum repository for RHEL/CentOS supports a rather out of date version of postgres.  If your needs require a more recent postgres version, then use the alternative install method which leverages a dedicated more recent yum repository. 

postgres service doesn't allow accounts to login with password by default.  Instead it treats a dedicated Linux/Unix postgres account special, allowing only that account to gain access to the service postgres# prompt. 

Although not required, it is recommended to create a Unix/Linux account, matched to a postgres role/user, matched to a postgres database table of the same name.  This makes it easier to login remotely as postgres defaults role/user to current Unix/Linux user, and expects connections to immediately associate to a database name same as username.  Otherwise petulant login connection errors tend to occur unless special care is taken to override default login connection behavior. 

Part 1 - standard install

# yum install postgresql-server postgresql-contrib

# postgresql-setup initdb

  [ for some reason need to start stop before editing pg_hba.conf
    Sometimes may need to rerun this after rm -fr /var/lib/pgsql/data/* ]

# vi /var/lib/pgsql/data/pg_hba.conf

  [ make sure pg_hba.conf file ownership permissions don't change lest service
    restart may throw cryptic Unregistered Authentication Agent error ]

# rcsdiff /var/lib/pgsql/data/pg_hba.conf

  82c82,83
  < host    all             all             127.0.0.1/32            ident
  ---
  > host    all             all             127.0.0.1/32            md5

  [ md5 enabled account to authenticate with a password.  It is okay
    to have multiple host lines to enable additional IP interface access ]

# systemctl start postgresql

# systemctl enable postgresql

# systemctl list-unit-files | grep -i postgre

  postgresql.service enabled 

# su - postgres

$ psql

#postgres=# \du

   Role name |                   Attributes                   | Member of 
  -----------+------------------------------------------------+-----------
   postgres  | Superuser, Create role, Create DB, Replication | {}

#postgres=# \q 

Part 2 - alternative install

Install postgres 9.6 on CentOS 7.x

• Find the latest RPM at PostgreSQL RPM Building Project
  e.g. pgdg-centos96-9.6-3.noarch.rpm
• Check if any prerequisite other repositories may be needed.  Perhaps unrelated, but following came after remi repository (PHP 5.6 enabling) was already enabled.

# rpm -Uvh pgdg-centos96-9.6-3.noarch.rpm

# yum install postgresql96-server postgresql96-contrib

# ls /var/lib/pgsql/9.6/data/

# /usr/pgsql-9.6/bin/postgresql96-setup initdb

  Initializing database ... OK

# ls /var/lib/pgsql/9.6/data/

  base
  ...
  postgresql.conf

# systemctl status postgresql-9.6

# systemctl start postgresql-9.6

# systemctl enable postgresql-9.6

  Created symlink from
  /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service
  to /usr/lib/systemd/system/postgresql-9.6.service.

# su - postgres

$ cd /var/lib/pgsql/9.6/data

$ rcsdiff pg_hbs.conf

  82c82
  < host  all  all  127.0.0.1/32  ident
  ---
  > host  all  all  127.0.0.1/32  md5

$ exit

# systemctl restart postgresql-9.6

# su - postgres

$ psql

  [ create superuser admin
    create rickatech user + database,
    give both users passwords to allow remote login ]

# CREATE USER rickatech;

# CREATE DATABASE rickatech OWNER rickatech;

# \password rickatech

# CREATE USER admin;

# ALTER USER admin WITH SUPERUSER;

# \password admin

# \du

   Role name |                 List of roles Attributes
  -----------+------------------------------------------------------------
   admin     | Superuser
   postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
   rickatech |

$ exit

# exit

$ whom

  rickatech

$ psql

# \q

$ psql -h 127.0.0.1

  password: 

# \q

$ psql -h 127.0.0.1 -U admin postgres -W

# \q

Create Databases and User Accounts

postgres service doesn't allow accounts to login with password by default.  Instead it treats special Linux/Unix system like named account postgres special, allowing only that account to gain access to the service postgres# prompt. 

phpPgAdmin

phpPgAdmin is a web application user interface that greatly helps manage postgres databases, accounts, and many other settings.  Before it can be enabled, postgres must be configured to allow some sort of remote login for selected accounts.  It is very important to be consistent is the setup of the service and the web application, lest cryptic errors may present when trying to login to the web application.

# yum install phpPgAdmin

# cat /etc/httpd/conf.d/ssl_postgres.conf 

  <Directory "/public/postgres/site">
      Options Indexes FollowSymLinks
      AllowOverride None
      Require all granted
  </Directory>

  <VirtualHost *:443>
      DocumentRoot "/public/postgres/site"
      ServerName postgres.cooldomain.com:443
      ...
  </VirtualHost>       

# ls -lh /public/postgres/

  site -> /usr/share/phpPgAdmin

# rcsdiff /etc/phpPgAdmin/config.inc.php

  18c18
  < $conf['servers'][0]['host'] = '';
  ---
  > $conf['servers'][0]['host'] = '127.0.0.1';

# rcsdiff /var/lib/pgsql/9.6/data/pg_hba.conf 

  82c82
  < host  all  all  127.0.0.1/32  ident
  ---
  > host  all  all  127.0.0.1/32  md5

# systemctl restart httpd  

Amazon Linux

2017-05 rickatech

For Amazon Linux and other Linux distros, going to ... and unzipping the files may be needed. Point httpd vhost file appropriately, and make a few config changes to config.inc.php.

$ unzip /public/phpPgAdmin-5.1.zip

  [ after downloading latest zip file ]

$ rcsdiff phpPgAdmin-5.1/conf/config.inc.php

  18c18
  < 	$conf['servers'][0]['host'] = '';
  ---
  > 	$conf['servers'][0]['host'] = 'proloop.ckyj1meiloyg.us-east-1.rds.amazonaws.com';
  93c93
  < 	$conf['extra_login_security'] = true;
  ---
  > 	$conf['extra_login_security'] = false; 

Frequently Used Commands

# su - postgres

$ whoami

  postgres

$ psql

postgres=# \du

  [ list user accounts ]

postgres=# \l

  [ list databases ]

postgres=# \q  

$ psql -h 127.0.0.1 -U testuser rick_db

# CREATE DATABASE mynewdb OWNER rick;

# \l

  Name
  -----------
  mynewdb

# DROP DATABASE mynewdb;

# \l  

# CREATE USER tester WITH PASSWORD 'test_password';

# GRANT ALL PRIVILEGES ON DATABASE "test_database" to tester;  

Backup, Restore

Backup, just schema

$ pg_dump -h host.domain.net -U admin -s -v -O -c -d dbname -f dbname_sch.sql 

Backup, data and schema

$ pg_dump -h host.domain.net -U admin    -v -O -c -d dbname -f dbname_sch_dt.sql 

Restore

$ psql -U admin dbname -f dbname_sch_dt.sql 

tablet | printable