New copy_db Script
There is a new script for copying databases. It’s a bit simpler than the Ansible playbook currently in use.
The basic idea is that you do this:
copy_db <source_host> <source_db> <target_host> <target_db>
If you want the target to be created, add a --create:
copy_db --create <source_host> <source_db> <target_host> <target_db>
Details can be found in the documentation.
Important note:
The script is in the tocco-dotfiles Git repository which I moved to GitLab. In order for the new script to be available change the remote to GitLab and pull the newest version:
cd ${PATH_TO_TOCCO_DOTFILES_REPO}
git remote set-url origin git@gitlab.com:toccoag/tocco-dotfiles.git
git pull
Password-less Authentication
As I mentioned during review a while back, databases can now be accessed without providing any credentials. Here a short recap of what this means.
Examples
What’s important for all of the following commands, is that you don’t specify a host
(-h $hostname) or a user (-U $user).
Connecting to a DB via psql:
$ psql ${db_name}
If you don’t know the DB name, connect to the maintenance db postgres and list all DBs:
$ psql postgres
$ \l+
Shortcut to connect via SSH directly to the DB:
$ ssh -t ${db_host} psql ${db_name}
Dump a database:
pg_dump -Fc -f ${dump_file} ${db_name};
Restore a database:
psql postres -c 'CREATE DATABASE ${db_name} OWNER ${db_user}'
pg_restore --role ${db_user} --no-owner --no-acl -d ${db_name} ${dump_file}
Or both, dump and restore, combined to copy a DB
psql postres -c 'CREATE DATABASE ${target_db_name} OWNER ${target_db_user}'
pg_dump -Fc -Z 0 ${source_db_name} | pg_restore --role ${target_db_user} --no-owner --no-acl -d ${target_db_name}
Again, it’s important that you don’t specify a host (-h $host) or user (-U $user). Only then
will password-less authentication work as expected.
For those that prefer, connecting to an installation’s database is also possible via OpenShift client:
$ oc rsh -c nice dc/nice psql
Documentation:
Technical Background
When no host is specified, psql, pg_dump, pg_restore and any other tool using the
libpq library to connect to Postgres will try to connect via Unix Socket at /var/run/postgresql/.s.PGSQL.5432.
Postgres, using it’s default configuration, will allow a connection via Unix Socket whenever the name
of the Postgres and Linux user match.
For instance, on my machine, I’m logged in as user “user”:
$ id
uid=1000(user) gid=1000(user) groups=1000(user),126(docker),999(qubes)
When I connect using psql without specifying a user, my Linux user name is used:
$ psql postgres
$ SELECT current_user;
current_user
--------------
user
(1 row)
As we see, the user names match and, as a result, password-less login works.
In essence, all that was needed to get this working was to create a Postgres user whose name matched your Linux user’s name. This was done via Ansible.