My postgres server is running in a windows server and three tablespace are created. Postgresql server is installed in C: drive and the tablespaces are in D: and E: drive. So how to take a backup and restore. Is it similar to normal backup and while restore, in another machine, do i need to have the same setup or i can restore it in a single drive? From the tablespace folder, is it possible to retreive information for only one table which is huge?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
closed as too localized by dezso, Max Vernon, StanleyJohns, RolandoMySQLDBA, Mark Storey-Smith Jun 20 '13 at 9:24
This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.
There are a few backup options for you.
A standard dump (pg_dump) will be restorable on another machine but you may want to create the tablespaces first before restoring. Where the tablespaces are is not really a concern for the recovery. Note you can also do pg_dump -Fc and this will allow you to select tables to restore at restore time.
Another option is to use pg_basebackup if you are on 9.1 or higher and this will rebuild your data directories over replication. See http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html for more on this option.