• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

how can i rescue a db postgres

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
tenriquez39
Asked:
tenriquez39
  • 4
  • 4
1 Solution
 
ivanovnCommented:
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
 
earth man2Commented:
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
 
tenriquez39EngenierAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ivanovnCommented:
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
 
tenriquez39EngenierAuthor Commented:
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
 
ivanovnCommented:
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
 
tenriquez39EngenierAuthor Commented:
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
 
ivanovnCommented:
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
 
tenriquez39EngenierAuthor Commented:
thanks ivanovn

i did you tell me

i complete missing pieces and now it run fine

thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now