Ask Ubuntu is a question and answer site for Ubuntu users and developers. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I lost my ubuntu partition (12.04) and I've installed the ubuntu 14.04, I could save my database files from postgreSQL v9.1, but now I'm with a problem how can I import this data base and my settings to this postgreSQL that came with Ubuntu (14.04) that is the 9.3.4.

  • UPDATE *

I've installed a VirtualBox Machine with Ubuntu 14.04 and installed PostgreSQL 9.1, I've copied the folder /var/lib/postgresql/9.1/main to the Ubuntu_VirtualBox, I could start the server (just a tip: chown postgres:postgres 9.1 and chmod -R 700 9.1) and I did the pg_dump -Ft database > database.tar I exported the sql file to my work ubuntu and tried to pg_restore -Ft -C database.tar it show up everything that is on the file at my screen but if I run psql -l the database that I've imported don't appear, so I came back to the Ubuntu_Virtualbox and did pg_dump database > database.sql and tried to import it doing a new database and import using the phppgadmin this appeared to me.

Erro de SQL:

ERROR:  syntax error at or near ")"
LINE 1: SELECT COUNT(*) AS total FROM () AS sub

No bloco:
SELECT COUNT(*) AS total FROM () AS sub

Well, in virtualbox I've edited /etc/php5/apache2/php.ini and set the configuration upload_max_filesize = 30M from 2M to 30m and I did the export again using the phppgadmin with the option data and estrcture type sql and I could import it.

I'm still testing...

share|improve this question
Do you not have backups? – Brian.D.Myers Jul 9 '14 at 3:11
You have to install 9.1. – Craig Ringer Jul 9 '14 at 10:52
My last back was from 1 week past the incident, so many things lost :( --- Craig Ringer, and after, put the folder in there and make a backup? – André M. Faria Jul 9 '14 at 11:31
Yes, install 9.1 along side 9.3, connect up the old DB, pg_dump and then import into 9.3. The standard process for major version point upgrades in pg. Then you can uninstall 9.1 if you want. – Brian.D.Myers Jul 9 '14 at 17:36
I've installed 9.1, connected the old DB, did the backup and I tried to import but it don't appear in my PostgreSQL. – André M. Faria Jul 9 '14 at 19:06

For your first try when the DB was not created :

pg_dump database will create all the SQL statements to recreate tables, indexes, functions, ... and data but without the CREATE DATABASE statement.

You use pg_restore with -C to recreate the database, which only works if you also give the name of the DB using the -d switch, like this :

pg_restore -Ft -C -d database database.tar
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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