Solved

how can i rescue a db postgres

Posted on 2013-02-05
9
527 Views
Last Modified: 2013-02-22
hi i have a problem

i have a pc with a Postgress database, but my problem is...the deskcomputer where i have the postgress server  crunch, and i have only sql backups files.

i don't know the database and tables structures, i only have the sql backup files
some one colud help me to know how restore this database to a new postgressql server?

thanks a lot a lot
2013-01-27.zip
2013-01-27.sql
0
Comment
Question by:tenriquez39
  • 4
  • 4
9 Comments
 
LVL 10

Expert Comment

by:ivanovn
ID: 38861746
The sql file you've posted already contains all of the CREATE statements that will generate all of the tables, functions, and types and load them with data. All you need to do is create a new empty database on a different server and execute that sql file.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 38861809
c:\Program Files\Postgresql\9.0\bin\psql.exe -f 2013-01-27.sql

c:\Program Files\Postgresql\9.0\bin\psql.exe --help
0
 

Author Comment

by:tenriquez39
ID: 38862062
hi

i'm running  the query, but i have error on this line


COPY bitacora (fecha, usuario, tabla, accion, id_registro) FROM stdin;
2011-10-01 23:00:20.174      lcuenca      pedido      INSERT      60087
2011-10-01 23:00:20.174      lcuenca      productospedido      INSERT      105949
2011-10-01 23:00:20.174      lcuenca      productospedido      INSERT      105950
2011-10-01 23:00:20.174      lcuenca      pedido      INSERT      60088
2011-10-01 23:00:20.174      lcuenca      productospedido      INSERT      105951
2011-10-01 23:00:20.174      lcuenca      productospedido      INSERT      105952
2011-10-01 23:00:20.174      lcuenca      productospedido      INSERT      105953


the error is :

ERROR:  error de sintaxis en o cerca de «2011»
LINE 28767: 2011-10-01 23:00:20.174 lcuenca pedido INSERT 60087
            ^

********** Error **********

(spanish)  it's mean.... sintax error in or near 2011


i'm debugging some querys errors , but i don't know how debug it...and there are various lines with this structure

have you some idea how can i debug it?

thanks a lot
0
 
LVL 10

Expert Comment

by:ivanovn
ID: 38864246
Are you running it from the console like earthman2 suggested or through pgAdmin?

You should be using the console. I just ran it on my test setup and it created 22 tables and loaded them with data with only errors being the missing role. If you use pgAdmin you will get the syntax error you are describing above.

To run it from a console in windows, open a command prompt and execute earthman2's command:
psql.exe -f 2013-01-27.sql

To run it from a console in linux, open an SSH session and execute the same command.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:tenriquez39
ID: 38865231
hi ivanovn

i made from console but send me passwrord wrong

then i open de sql file , and let me advance till that error

what can is it?

thenks a lot


C:\Program Files\PostgreSQL\9.2\bin>psql.exe -f2013-01-27.sql
Contrase±a:
psql: FATAL:  la autentificación password falló para el usuario «aenriquez»

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -f2013-01-27.sql
Contrase±a:
psql: FATAL:  la autentificación password falló para el usuario «aenriquez»

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -f2013-01-27.sql
Contrase±a:
psql: fe_sendauth: no password supplied

C:\Program Files\PostgreSQL\9.2\bin>
0
 
LVL 10

Expert Comment

by:ivanovn
ID: 38865254
You need to supply additional parameters to your psql command. See the documentation for all available options: http://www.postgresql.org/docs/9.2/static/app-psql.html

For example, if the DB you are restoring the data to is located on your local machine and is called "testdb", the user is "testuser" and password is "testpassword" the command would look something like this:

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -h localhost -d testdb -U testuser -f 2013-01-27.sql

After you execute that, you will be prompted for the password, and you would type in testpassword.
0
 

Author Comment

by:tenriquez39
ID: 38865504
hi ivanovn

thans a lot a lot

i think we advance...

i run so:

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -h localhost -d cat2 -U postgres -f
2013-01-27.sql

now....run, and create 22 tables, but i see some tables, and only record one record..example

example, i attach an image with data view from productospedido table

this was the command results  at the command dos :

ALTER TABLE
 setval
--------
   1012
(1 fila)


 setval
--------
  39736
(1 fila)


CREATE TABLE
ALTER TABLE
 setval
--------
    900
(1 fila)


CREATE TABLE
ALTER TABLE
 setval
--------
    168
(1 fila)


CREATE TABLE
ALTER TABLE
 setval
--------
 129552
(1 fila)


CREATE TABLE
ALTER TABLE
 setval
--------
   5635
(1 fila)


 setval
--------
  60663
(1 fila)


psql:2013-01-27.sql:2313: ERROR:  la relación «perfiles_id_seq» ya existe
ALTER TABLE
 setval
--------
     13
(1 fila)


CREATE TABLE
ALTER TABLE
 setval
--------
  14750
(1 fila)


CREATE TABLE
ALTER TABLE
 setval
--------
     10
(1 fila)


CREATE TABLE
ALTER TABLE
 setval
--------
     20
(1 fila)


CREATE TABLE
ALTER TABLE
 setval
--------
   7438
(1 fila)


 setval
--------
 119012
(1 fila)


psql:2013-01-27.sql:2441: ERROR:  la relación «reenvios_id_seq» ya existe
ALTER TABLE
 setval
--------
  65302
(1 fila)


CREATE VIEW
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
 setval
--------
     45
(1 fila)


 setval
--------
   6235
(1 fila)


CREATE VIEW
ALTER TABLE
CREATE TABLE
ALTER TABLE
 setval
--------
     11
(1 fila)


psql:2013-01-27.sql:2566: ERROR:  no existe la columna pg_authid.rolconfig
LÍNEA 2: ..., (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid....
                                                              ^
psql:2013-01-27.sql:2569: ERROR:  no existe la relación «public.usuaria»
psql:2013-01-27.sql:2576: ERROR:  no existe la columna pg_authid.rolconfig
LÍNEA 2: ..., (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid....
                                                              ^
psql:2013-01-27.sql:2579: ERROR:  no existe la relación «public.usuario»
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT
REVOKE
REVOKE
GRANT

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -h localhost -d cat2 -U postgres -f
2013-01-27.sql




you know what can i do??

thanks a lot a lot a lot
data-view-productospedido-table.jpg
0
 
LVL 10

Accepted Solution

by:
ivanovn earned 500 total points
ID: 38868020
Looking at your output, there are a few errors being thrown for sequences that don't exist. I don't have your dump script handy, but my guess is they are not in the backup file, or the user that owns them isn't created or something along those lines.

What you can do is look through errors and create those missing pieces manually and then re-run the dump script. I would suggest that you clear/delete the DB you restored to first.

So for example for this error:
ERROR:  la relación «perfiles_id_seq» ya existe

In the empty DB you would create perfiles_id_seq sequence. Then go on and do the same for all other errors and re-run the dump script.
0
 

Author Closing Comment

by:tenriquez39
ID: 38920129
thanks ivanovn

i did you tell me

i complete missing pieces and now it run fine

thanks a lot
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now