Home / ubuntu / Questions / 493407
In Process
André M. Faria
Asked: 2014-07-09 18:25:01 +0800 CST

PostgreSQL importing from folder

  • 772

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

12.04

1 Answers

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