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
Applying parameters
You can apply the same parameters for the output of the textdump as for restoring the dump (for e.g. --clean to drop database objects before recreating them):
pg_restore --clean $dumpname -f $name.textdump
PostgreSQL 11 and older
You can output the contents to STDOUT and redirect it to a file:
pg_restore $dumpname > $name.textdump
Related cards:
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...
HowTo: Add custom headers to docker client requests
It is possible to add custom http header to request made by the docker CLI client. This can be used to e.g. add another authentication header for a proxy.
Implementation
c...
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...
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...
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...
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...
Build and install exim4-daemon-custom on Ubuntu 16.04
Unfortunately I couldn't find a complete and working documentation on how to do this. But this steps succeeded (at least once).
-
Install build dependencies
$ sudo apt-get install pbuilder devscripts dpatch grep-dctrl debhelper ```...
Use pg_repack to do a VACUUM FULL without holding an exclusive lock during processing
You can use pg_repack to do a VACUUM FULL without holding an exclusive lock during processing.
There is still a need of one exclusive lock at the beginning and the end of the repacking process. But in the time between, you can use the affecte...
HowTo apply Test Driven Development to Container Images
Apply Test Driven Development(TDD) to the process of building container images by defining test before writing code and automate the testing process. Iterate through the TDD cycle while developing and running the tests later in continuous integrat...
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...
