Unable to recreate a PostgreSQL DB from a provided .sql file

Question

I am completely new to the world of PostgreSQL, please be patient with me.

I've been provided with a .sql file (the whole file here: db-e4w.zip) which seems to be exported via Navicat from PGSQL server. Start of the file (just in case anyone would try it the file is to be found here).

/*
Navicat PGSQL Data Transfer

Source Server         : pluto
Source Server Version : 80311
Source Host           : localhost:5432
Source Database       : kun3
Source Schema         : public

Target Server Type    : PGSQL
Target Server Version : 90100
File Encoding         : 65001

Date: 2012-06-07 11:24:41
*/

My problem is that I am unable to recreate the original database from the query contained in the provided file. I've tried via SQL command in pgAdmin III, Navicat PGSQL, and phpPgAdmin all with no success. I usually end up with hundreds of mistakes, one of the first is:

SQL error:
ERROR:  relation "core_log_log_id_log_seq" does not exist

Which is a result of this query (first of the sql file)

CREATE TABLE "core_log_log" (
"id_log" int4 DEFAULT nextval('core_log_log_id_log_seq'::regclass) NOT NULL,
"message_log" text,
"priority_log" int2,
"date_creation_log" timestamp(6) DEFAULT NULL::timestamp without time zone,
"server_log" char(255) DEFAULT NULL::bpchar,
"idusr_log" int4,
"exception_log" text,
"idvis_log" int4
)
WITH (OIDS=TRUE)

;

Any help would be highly appreciated. Even if someone would just come up with something like the file is corrupted or incomplete, get a new and proper one or you are riding a completely false horse - try to import it this other way would help a bunch.

Thanks a lot.

Nick Veys · Accepted Answer · 2012-06-19 01:30:09Z

1

If there is nothing like:

CREATE SEQUENCE core_log_log_id_log_seq ...

In there before it is referencing the sequence, I would think it to be incomplete.

answered Jun 19, 2012 at 1:30

Nick Veys

24k5 gold badges49 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ddhh Over a year ago

Thanks again. Now before jumping on learning sequences, is there a way to get a complete database dump (sql file) which would allow me to recreate the original database without any edits. I aware of that this is not directly related to the solution of procesing the original SQL and therefore a little misleading question, but before I'd start with fixing this provided SQL file, I'd preferably ask the provider for another - more complete version of it. Would you mind to share any hint of what is the safest way of exporting/importing PGSQL db I should ask them for?

Nick Veys Over a year ago

I would assume Navicat would allow a full export. I imagine it needs to be told to export sequences, since you may not always want to "really really" re-create the entire database, instead just re-build tables and data. I would think it'd just be an option somewhere in the export process to export sequences as well. Sorry, not a Navicat user! Best of luck!

ddhh Over a year ago

I will try to ask for that. Hope I will end up with a fully working file. If not, you'll have me back here soon. :) Thank you again.

Craig Ringer Over a year ago

@ddhh Ask them to use pg_dump to export the database if NaviCat doesn't do the job.

ddhh Over a year ago

I will try that. However I'm afraid it's not Navicat who does not do the job, it's them... We'll see. Thanks!

Collectives™ on Stack Overflow

Unable to recreate a PostgreSQL DB from a provided .sql file

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related