PostgreSQL
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
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.
# 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
Install postgres 9.6 on CentOS 7.x
# 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 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