Manually create Postgres password hash
For some reason you have to pass the password hash if you want to create a role in postgres. To calculate the hash use the following snippet:
echo -n "md5"; echo -n "${PG_PASSWORD}${PG_USERNAME}" | md5sum | awk '{print $1}'
Related cards:
create htpasswd entry and print to stdout
Create htpasswd entry and print to stdout:
$ sudo apt install apache2-utils # Optional
$ htpasswd -n $USERNAME
New password:
Re-type new password:
$USERNAME:<Password Hash>
dumping and restoring PostgreSQL databases
This card is just about creating simple PostgreSQL dumps. This is no instruction for a backup strategy nor a guide for SQL dump performance optimization.
Read before starting
- I will assume that all commands will be executed as local `postgr...
FAQ for When PostgreSQL Indexes Are Corrupted After Locale Changes
When changing glibc versions, it's possible to end up with corrupt indexes in PostgreSQL.
Are My Indexes Affected?
If your database has...
Controlling multiple PostgreSQL installations on Debian/Ubuntu
Hint
This applies only to distributions based on Debian.
Requirement
You need to setup the PostgreSQL Apt Repository first.
If you have multiple Postgres versions install...
Useful PostgreSQL commands
on the bash (issued as postgres user)
Start/Stop/Restart PostgreSQL
pg_ctl -D $configdir start|stop|restart
Start/Stop/Restart the corresponding PostgreSQL using the given configuration directory. The configuration directory sh...
Restoring old Postgres dumps with pg_restore v11 and higher
There is an issue with when restoring a PostgreSQL dump created with pg_dump
< v11 with pg_restore
> v10:
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public"
already exists
Command was: CREATE SCHEMA public;
`...
HowTo: Get postgres shell in kubernetes
If your postgres database is only accessible from inside a kubernetes cluster, e.g. if it's configured in AWS RDS and not available to the public (as it should be!), here's how to open a psql
shell inside Kubernetes and connect to the database. ...
Mind your locales with glibc upgrades when using PostgreSQL
When changing the glibc version, it's possible that the upgrade also includes changes to how locales work.
This is especially relevant when using [PostgreSQL databases (of any version)](https://wiki.postgresql.org/wiki/Locale_data_changes#What_in...
Merge two PostgreSQL dumps
Attention
This is an edge-case. You probably don't want to mix different database dumps. It also requires that the mixed tables do not share relations to other database objects.
Scenario
- You've got two database dumps:
- `dump_a.pgdum...
convert PostgreSQL custom dump format to textdump
If you have a PostgreSQL dump in the custom format you can can view the text format dump (plain SQL statements).
PostgreSQL 12 and newer
Write it as a textdump to a file:
pg_restore $dumpname -f name.textdump