Can PosgreSQL databases be restored from physical files?

Question

We had an EC2 instance running PostgreSQL. We created a new EBS volume for our more important DBs and stored them on the new volume by creating new tables spaces and setting their location. The system DBs were left in the default tablespace and location. Unfortunately the instance was accidentally terminated. We are trying to restore PostgreSQL with the databases.

The databases were on the EBS volume. We have spun up a new instance and have mounted the EBS volume and we can see the files. Is there any way we can get our files back?

• András Váczi: If you don't have PostgreSQL running on your new instance, install/start it. Then it should be fairly easy to set data_directory to your EBS volume mount. Also, what do you mean by VM?
• uriDium: @dezso We tried that, it didn't work. by VM I mean AWS EC2 VM. I think that might not work because when we created the database we used a different tablespace. (I remember we used a different location). All the system databases were left in the standard installation location.

Answer 1

As dezso said, its easy to change the data directory.

But I did a small PoC for this. So its possible to use existing physical files to another Postgresql.

Limitations

• The new PostgreSQL should be the same version.
• New PostgreSQL data directory and custom tablespace directory should be same.

My Postgresql config:

• OS: Ubuntu 16.04
• Postgresql 9.4

Install and config a sample database

apt-get install postgresql-9.4
mkdir /opt/pgdata
chown -R postgres:postgres /opt/pgdata
--in psql
create tablespace db LOCATION '/opt/pgdata/';
create database bhuvi with tablespace db;
\c bhuvi
create table test (id int);
insert into test values (1),(2),(3),(4),(5);
bhuvi=# select * from test;
 id
----
 1
 2
 3
 4
 5
(5 rows)

Remove the PostgreSQL

--take a copy of Data direcory and table space directory
cp -R /var/lib/postgresql/9.4/main/ /opt
cp -R /opt/pgdata /opt/pgdata_bak
--clean up postgresql
apt-get remove --purge postgresql-9.4
apt-get autoclean
apt-get autoremove
rm -rf /etc/postgresql
rm -rf /var/lib/postgresql/
rm -rf /var/log/postgresql/
rm -rf /opt/pgdata 

Fresh install and Replace old data and Tablespace directories

-- Reinstall
apt-get install postgresql-9.4
service postgresql stop
rm -rf /var/log/postgresql/9.4/main/
cp -R /opt/main /var/log/postgresql/9.4/
cp -R /opt/pgdata_bak /opt/pgdata
chown -R postgres:postgres /var/log/postgresql/9.4/main/
chown -R postgres:postgres /opt/pgdata
service postgresql start

Now check the new Postgresql

--in psql
\l+
 Name | Owner | Encoding |
-----------+----------+----------+
 bhuvi | postgres | UTF8 |
 postgres | postgres | UTF8 |
 template0 | postgres | UTF8 |
 | | |
 template1 | postgres | UTF8 |
 | | |
bhuvi=# \dt
 List of relations
 Schema | Name | Type | Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)
bhuvi=# select * from test ;
 id
----
 1
 2
 3
 4
 5
(5 rows)

• uriDium: The data_directory was in the standard directory on the VM. /var/postgres/9.3/ and the tablespace I created on the EBS volume and mounted it t /data/dbs. In the limitations, are you saying that the limitation was the existing setup, or that the new setup the data_directory and tablespace must be in the same dir. We have no control over previous, but I am happy to move to new layout. EDIT: Sorry I see you said new. Read too quickly
• uriDium: In your sample I see you are copying over what you originally had in the data_directory. We no longer have access to the original data directory, only the files in the new tablespace. If we installed a brand new Postgres and then just copied the tablespace files in the new data directory should that work?
• TheDataGuy: I did on more PoC, I'll update the answer, Its possible