Restoring Postgres database without pg dump?

Question

I have a postgre database DATA1 in table space location D:\tbl_DATA1. We use OS backup restore tool copy the the D:\tbl_DATA1 to a target machine C:\tbl_DATA1. Is it possible for recreate the database from this folder in the second mahcine?

Is it a tablespace? or a whole PostgreSql database? Does it contain the directories pg_clog, pg_xlog, and global? If so, you can just copy it and start PostgreSQL from it. If not, you're boned because a tablespace is pretty meaningless without the shared catalogs, transaction commit logs, etc. — Craig Ringer
– Craig Ringer, Commented Aug 3, 2017 at 1:35

Vao Tsun · Accepted Answer · 2017-08-02 08:38:23Z

3

https://www.postgresql.org/docs/current/static/backup-file.html

An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database

and later two restrictions mentionned

The database server must be shut down in order to get a usable backup.
You should resotore the whole PGDATA direcotory, not the certain individual tables or databases from their respective files or directories.

So yes - it is a common practice to shutdown the PostgreSQL, copy PGDATA directory to other machine and start Postgres in order to get the cluser copy. But it is done cluster level - not tablespace as you mention or database - the whole data_directory should be copied.

So no - copying the tablespace directory and trying to hack the db to add a tablespace will fail.

answered Aug 2, 2017 at 8:38

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Restoring Postgres database without pg dump?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related