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?

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)