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;
Convert
If you want to restore this dump you should convert the dump to the text format first and comment out the CREATE SCHEMA public; statement. For further information see linked content.
Use different pg_restore
After adding the PostgreSQL
APT respository
Show archive.org snapshot
you can install additional client versions on your local machine with apt install postgresql-client-$VERSION. You'll find them here: /usr/lib/postgresql/$VERSION/bin/pg_restore.
Also see our card on importing Postgres dumps created with newer versions.
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...
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...
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...
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...
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...
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...
HowTo: Clone a MariaDB database with mariabackup, mbstream and netcat
If you have a very large datadir in MariaDB and you want to transfer the data to another host (e.g. for replication) you may want to avoid storing it locally and copying it between the hosts.
You can stream the backup directly via netcat.
##...
HowTo: Curl applications that are usually behind reverse proxies with TLS termination without the application redirecting to https schema
A lot of web applications require being called over https, which is a good thing. It's possible to configure this requirement at the web- or proxy server level, where nginx or apache will just redirect every request on http to https. Som...
