• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • 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
 
tenriquez39Author 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

i did you tell me

i complete missing pieces and now it run fine

thanks a lot
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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