PostgreSQL point in time recovery – experiences

Sources: PostgreSQL PITR not working properly How to perform PostgreSQL point-in-time recovery Fortunately so far I used PostgreSQL PITR recovery only for testing purposes. But experiences are interesting so I want to record them....

pg_basebackup – experiences

pg_basebackup version <10 does not allow streaming of WAL files during tar files so it is technically useful mainly for cloning of the database for replication. To use it for backups it is necessary to use it with pg-barman. pg_basebackp version 10+ has this...

Move PostgreSQL tablespace / data directory to another disk

I had to face several time situation like this – existing disk is suddenly too small and we need to move to bigger one. And for some reason there is no time to do it properly – like stopping database for several hours and migrating data to another machine...

pg_dump & pg_restore

Here are some often used variants of these commands: pg_dump: dump the whole database (custom format): pg_dump --username "user_name" --no-password&nbsp; --format custom --verbose --file "file_name.backup" "database_name" dump only...

Export data into CSV format

Command “COPY” will do all the dirty job.   Attention – examples are ment for Linux and contain variables which you have to set or substitute with values.   If data are small you will probably use directly unpacked form: psql -U postgres -d...