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...
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...
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...
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;
`...
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...
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...
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 ```...
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...
When to use a function over a defined in Puppet
In Puppet, there are some differences between defined and function types when it comes to code encapsulation. In most cases, a defined type is best, but there are some situations where a function is more appropriate.
Use a defined type when:...
