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?

  • 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?
    – dezso
    Dec 20, 2017 at 14:34
  • @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.
    – uriDium
    Dec 20, 2017 at 14:46

1 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.


  • 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;
(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
   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 ;
(5 rows)
  • 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
    Dec 20, 2017 at 19:19
  • 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?
    – uriDium
    Dec 20, 2017 at 19:22
  • I did on more PoC, I'll update the answer, Its possible
    – TheDataGuy
    Dec 21, 2017 at 5:56

Your Answer

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

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