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 .. code-block:: bash # 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 ^^^^^^^^^^^^ `PG Docs: SQL Dumps `_ .. code-block:: bash # 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 .. code-block:: bash 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: .. code-block:: bash # 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 .. code-block:: bash # 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: https://www.postgresql.org/docs/12/libpq-pgpass.html .. code-block:: bash # .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 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. code-block:: bash # 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@* Postgres Queries ---------------- .. toctree:: :maxdepth: 1 queries.rst