1

I would intuit that it's fine, but I just want to make sure there are no gotchas from a recovery point of view:

If I were to lose my temp tablespace upon system crash, would this prevent proper crash recovery?

Also, if I were to omit the temp tablespace from the base backup, would that prevent proper backup recovery?

2 Answers 2

1

If you are referring to the base/pgsql_tmp, then you should be fine. But I don't speak from experience of having done that myself. The only gotcha is that you have to make sure that the location is accessible when your PostgreSQL server starts up.

(Ref.: Book PostgreSQL 9.0 High Performance, page 93).

The book in question refers to creating a simlink of the base/pgsql_tmp on the OS partition or other less "safe" partition (i.e. non-RAID or the like).

0
0

This is what the official documentation for PostgreSQL states:

Temporary files (for operations such as sorting more data than can fit in memory) are created within PGDATA/base/pgsql_tmp, or within a pgsql_tmp subdirectory of a tablespace directory if a tablespace other than pg_default is specified for them. The name of a temporary file has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning backend and NNN distinguishes different temporary files of that backend.

Reference: 65.1. Database File Layout / PostgreSQL Documentation (current)

There is no further reference on the pgsql_tmp directory in the official documentation in regards to backup and restore.

The restore procedure as documented by PostgreSQL is as follows:

Okay, the worst has happened and you need to recover from your backup. Here is the procedure:

  1. Stop the server, if it's running.

  2. If you have the space to do so, copy the whole cluster data directory and any tablespaces to a temporary location in case you need them later. Note that this precaution will require that you have enough free space on your system to hold two copies of your existing database. If you do not have enough space, you should at least save the contents of the cluster's pg_xlog subdirectory, as it might contain logs which were not archived before the system went down.

  3. Remove all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.

  4. Restore the database files from your file system backup. Be sure that they are restored with the right ownership (the database system user, not root!) and with the right permissions. If you are using tablespaces, you should verify that the symbolic links in pg_tblspc/ were correctly restored.

  5. Remove any files present in pg_xlog/; these came from the file system backup and are therefore probably obsolete rather than current. If you didn't archive pg_xlog/ at all, then recreate it with proper permissions, being careful to ensure that you re-establish it as a symbolic link if you had it set up that way before.

  6. If you have unarchived WAL segment files that you saved in step 2, copy them into pg_xlog/. (It is best to copy them, not move them, so you still have the unmodified files if a problem occurs and you have to start over.)

  7. Create a recovery command file recovery.conf in the cluster data directory (see Chapter 27). You might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery was successful.

  8. Start the server. The server will go into recovery mode and proceed to read through the archived WAL files it needs. Should the recovery be terminated because of an external error, the server can simply be restarted and it will continue recovery. Upon completion of the recovery process, the server will rename recovery.conf to recovery.done (to prevent accidentally re-entering recovery mode later) and then commence normal database operations.

  9. Inspect the contents of the database to ensure you have recovered to the desired state. If not, return to step 1. If all is well, allow your users to connect by restoring pg_hba.conf to normal.

Reference: 25.3.4. Recovering Using a Continuous Archive Backup

This directory will be purged on PostgreSQL shutdown, so the temporary files contained in the pgsql_tmp directory aren't relevant for the restore process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.