Postgres Docs
PG 12 Docs Online
- Postgres Database:
a store of multiple tables, that has one owner
- Postgres User:
that can authenticate locally or over the network, and make queries. users can own databases, and can have multiple roles.
- Postgres role:
Exactly like a user, but the Login Flag is disabled by default. So CREATE ROLE and CREATE USER are almost identical commands
Summary of Tools and Technique
Docker Summary
here’s a quick summary of docker container commands
# bash into a container
docker exec -it pgmaster_pgserver_1 bash -i
# show the env of the container
docker exec -it pgmaster_pgserver_1 env
# help on certain pg commands
docker exec -it pgmaster_pgserver_1 pg_dump --help
# cat a single file, and maybe redirect it to a host file
file1=/etc/postgresql.conf
file2=/var/lib/postgresql/data/postgresql.conf
docker exec -it pgmaster_pgserver_1 cat "$file1"
docker exec -it pgmaster_pgserver_1 cat "$file2"
# copy a file from a running container
docker cp pgmaster_pgserver_1:/etc/postgresql.conf .
# copy a file into a running container
docker cp postgresql.conf pgmaster_pgserver_1:/back
Summary of Postgres Tools
PostgreSQL client applications will by default connect with the database user name that is equal to the current operating system user name. To override this, either specify the -U option or set the environment variable PGUSER
- psql:
Postgres interactive terminal. Can take statements through stdin. For example, via SQL commands generated by SQL Dump
- pg_dump:
Generate SQL Commands to recreate a single database. The best way to transfer a database across different PG versions, and the only way to transfer a database across PG on different architectures (for example, 32bit to 64bit, or worse)
- pg_dumpall:
Back up each database in a cluster and also preserve cluster-wide data such as role and tablespace definition.
Backup and Replication
Here is a link to the PG Replication and Backup solutions
1- SQL Dumps
# if using docker
docker exec -it pgmaster_pgserver_1 pg_dump -d mqx -U mqx > /bits/data/back.sql
# backup the database mk
pg_dump -d mqx > mqx.sql
# dump and compress for large databases
pg_dump -d mqx | gzip > mqx.sql.gz
# restore requires only the role, and not the database to be predefined
psql --set ON_ERROR_STOP=on mqx2 < mqx.sql
# backup and restore in a pipeline
pg_dump -d mqx | psql -d mqx2
# dump all databases in the cluster, including cluster-wide data
pg_dumpall > full-backup.sql
# and to restore, selecting the postgres database
psql -f full-backup.sql postgres
2- Filesystem backup
Danger
the server must be off during this method’s backup and restore
docker exec -it pgmaster_pgserver_1 bash -i
# from inside the container
test
mkdir -f /back
tar -cf /back/backup.tar /var/lib/postgresql/data
docker cp pgmaster_pgserver_1:/back/backup.tar .
3- Continuous Archiving and Point in Time Recovery (PITR)
Ubunto 20LTS postgresql-12
Bootstrapping a Fresh ubuntu 20 install
first, the postgres user should be able to access the db through locally through a unix socket without having to enter a password. In pg_hba.conf change:
# override (either replace or preceed)
local all postgres peer
# with this:
local all postgres trust
# and for your own username, locally and over the network
local all mk peer
host all mk 0.0.0.0/0 md5
- peer:
means a user can login without password only if they are a logged in UNIX
- trust:
means a user can login without any authentication, as long as they have a role in the pg database
- md5:
user can login with a password
The following code to create your own role in pg
# Create a ROLE that is also able to login
psql -U postgres -c "CREATE USER mk SUPERUSER ENCRYPTED PASSWORD \
'uFVa113JMVnITL/cv8/q+segmCt61zokiYn/ISMqMU8'"
# and now you can login as "mk" to to the default database.
# with a newly created user, you will need to include the -d postgres
# option, becuase a logged in user needs to land on a default database
psql -d postgres
# create an mk database
# you can pipe in your semi-colon-terminated SQL statements through to psql
echo "CREATE DATABASE mk;" | psql -d postgres
# now that db mk has been created, we can safely login to psql with no args
psql
# and even generate HTML outputt out of a query:
psql -d mqx -H -c "select * from person limit 10"
psql -d postgres -H -c "select * from pg_roles"
# List Roles, and their memberships in the database
psql -c '\dgS'
# Grant Roles to Login users
psql -c "GRANT mqx TO mk"
psql -c "GRANT mk TO postgres"
psql -c "GRANT mqx TO postgres"
Common pg tools and usages:
- The password file:
# .pgpass format is
# host: port: database: username: password
# somefields can take a wildcard *
echo "nuc:15432:*:mk:uFVa113JMVnITL/cv8/q+segmCt61zokiYn/ISMqMU8" \
| tee -a ~/.pgpass
# login remotely to nuc, username mk is assumed, database is redmine
pg_dump -h nuc -p 15432 redmine > /bits/data/backup/redmine$(date +%s).sql
# interactive pg shell on nuc
psql -h nuc -U redmine
Postgres Cluster Management with Ubuntu Tools
# create a cluster backup for postgres 12
sudo pg_createcluster -u 1000 -p 35432 12 backup
# then remove it
sudo pg_dropcluster 12 backup
# just a dirty one time start
pg_ctlcluster 12 backup start
# via systemd, to deploy if needed
sudo systemctl start postgresql@12-backup
sudo systemctl stop postgresql@12-backup
sudo systemctl status postgresql@12-backup
sudo systemctl status postgresql@12-main
sudo systemctl status postgresql@*