Thread: [LONG] Need help on pg_dump!
Hi all!
Something weird seems to be happening when I try to restore a db,
pg_dumped on PC 1, on a second PC.
PG vers. 7.2 is running on both PCs (under Debian/Woody, k 2.2.22).
Here follow some of the steps taken:
On PC no. 1:
===========
postgres$ pg_dump mydb > /home/ennio/db.out
then I ftped db.out to PC no. 2
-------------
On PC no.2:
==========
postgres$ pg_restore -C -d mydb /home/ennio/db.out
pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL 1: Database "mydb" does not exist in the
systemcatalog.
----------
I connect as user ennio and create mydb:
=> create database mydb with encoding='SQL-ASCII';
(I have to discover why it would not accept 'LATIN1' ...)
----------
=> \l
List of databases
Name | Owner | Encoding
-------------+----------+-----------
mydb | ennio | SQL_ASCII
odontolinux | postgres | UNICODE
template0 | postgres | UNICODE
template1 | postgres | UNICODE
(4 rows)
After creating mydb I try again:
pg_restore -C /home/ennio/db.out >>draft1 2>&1
finally obtaining:
quote:
-----------------------------------------------------------------------
pg_restore: [archiver] WARNING:
Data restoration may fail because existing triggers cannot be disabled
(no superuser user name specified). This is only a problem when
restoring into a database with already existing triggers.
--
-- Selected TOC Entries:
--
\connect - ennio
--
-- TOC Entry ID 1 (OID 0)
--
-- Name: mydb Type: DATABASE Owner: ennio
-- Data Pos: 0 (Length 0)
--
CREATE DATABASE "mydb" WITH TEMPLATE = template0 ENCODING = 7;
\connect mydb ennio
--
-- TOC Entry ID 2 (OID 47191)
--
-- Name: bibl_bug Type: TABLE Owner: ennio
-- Data Pos: 0 (Length 0)
--
CREATE TABLE "bibl_bug" (
"autore" character varying,
"titolo" character varying,
"altre_notizie" character varying,
"collana" character varying
);
[ cut ]
COPY "bibl_bug" FROM stdin;
TOUTAIN J. L'économie antique [Pr. acq. FB 377, pc. FB 400] L'Évolution de l'Humanité. Synthèse Collect.
Dirigéepar Henri Beer
ZWIRNER Giuseppe Istituzioni di Matematiche per gli studenti delle facoltà di chimica, agraria, scienze naturali,
economiae commercio e statistica. Parte prima: rist. dell'ottava ediz. riveduta ed ampliata con numerosi esercizi e
problemirisolti e proposti. 1973. Parte sec. 4^ ed.
\.
[ .............. cut .................]
--
-- Data for TOC Entry ID 67 (OID 53440)
--
-- Name: pro_chars Type: TABLE DATA Owner: ennio
-- Data Pos: 22728 (Length 67)
--
COPY "pro_chars" FROM stdin;
ALT+176 °
ALT+177 ±
ALT+178 ²
ALT+171 «
ALT+187 »
ALT+233 é
ALT+177 ±
\.
, paranormal , 0 , , VAUTOER Robert , I poteri magici della luna
, , Della Valle Editore. Torino, 1971. , Torino , 1971 , La Sfinge. 1. Direttore Stefano
Jacini. , 1 , , 228 , ril. ed. cop. fig. , 11.5 , 19.0 , 2500.00 ,
2500.00 , 0.00 , Siena , gen. 1987 , Bassi , , F , 0.00 ,
, \r
\.
--
-- Data for TOC Entry ID 69 (OID 57620)
--
-- Name: bib_lt Type: TABLE DATA Owner: ennio
-- Data Pos: 56530 (Length 31974)
--
COPY "bib_lt" FROM stdin;
0 varie 0 SERAFINI Maria Teresa Come si studia Con 6 pp. di catalogo
Bompiani.Milano, novembre 1989 Milano 1989 Strumenti Bompiani. Collana diretta da Umberto Eco 1
341 br. pl. 12.5 19.2 21600.00 24000.00 24000.00 Siena giu. 2000 Feltrinell
F 0.00
[ ................ cut ..................]
paranormal 0 VAUTOER Robert I poteri magici della luna Della Valle Editore.
Torino,1971. Torino 1971 La Sfinge. 1. Direttore Stefano Jacini. 1 228 ril. ed. cop. fig.
11.5 19.0 2500.00 2500.00 0.00 Siena gen. 1987 Bassi F 0.00
\.
--
-- Data for TOC Entry ID 70 (OID 59443)
--
-- Name: bibdbt_lt Type: TABLE DATA Owner: ennio
-- Data Pos: 88504 (Length 2975)
--
COPY "bibdbt_lt" FROM stdin;
[ ........... cut ............]
\.
--
-- Data for TOC Entry ID 71 (OID 61155)
--
-- Name: pippo Type: TABLE DATA Owner: ennio
-- Data Pos: 91479 (Length 412)
--
COPY "pippo" FROM stdin;
[ ,,,,,,,,,,, cut ...............]
PGBCOPY\n¿\r\n \t
\N
\N
\.
--
-- Data for TOC Entry ID 74 (OID 62123)
--
-- Name: pga_queries Type: TABLE DATA Owner: ennio
-- Data Pos: 96135 (Length 271)
--
COPY "pga_queries" FROM stdin;
ricerca_per_argomento S select * from bib_lt where argomento like '%[parameter "argomento?"]%'
\n
[ ................ cut .............]
\.
--
-- Data for TOC Entry ID 75 (OID 62128)
--
-- Name: pga_forms Type: TABLE DATA Owner: ennio
-- Data Pos: 96406 (Length 27)
--
COPY "pga_forms" FROM stdin;
\.
--
-- Data for TOC Entry ID 76 (OID 62133)
--
-- Name: pga_scripts Type: TABLE DATA Owner: ennio
-- Data Pos: 96433 (Length 27)
--
COPY "pga_scripts" FROM stdin;
\.
--
-- Data for TOC Entry ID 77 (OID 62138)
--
-- Name: pga_reports Type: TABLE DATA Owner: ennio
-- Data Pos: 96460 (Length 27)
--
COPY "pga_reports" FROM stdin;
\.
--
-- Data for TOC Entry ID 78 (OID 62143)
--
-- Name: pga_schema Type: TABLE DATA Owner: ennio
-- Data Pos: 96487 (Length 27)
--
COPY "pga_schema" FROM stdin;
\.
[ cut ]
------------------------------------------------------------------
unquote
Howeer, when I connect (ennio$ psql mydb) and do a
select * from any of the tables listed anove;
the structure of the fields only appears, with no data on them!:
=> select * from bib_lt;
n_prog | argomento | class_dewey | collocaz | autore | titolo | altre_notizie | editore | citta | data_pubbl | collana
|voll | n_pag_pref | n_pag_testo | car_tec_ed | largh_cm | alt_cm | pr_acq_lit | pr_listino | pr_copert | luogo_acq |
data_acq| libraro | note | scheda_ltr | val_merc | data_e_fonte | dummy
--------+-----------+-------------+----------+--------+--------+---------------+---------+-------+------------+---------+------+------------+-------------+------------+----------+--------+------------+------------+-----------+-----------+----------+---------+------+------------+----------+--------------+-------
(0 rows)
List of databases
Name | Owner | Encoding
-------------+----------+-----------
mydb | ennio | SQL_ASCII
odontolinux | postgres | UNICODE
template0 | postgres | UNICODE
template1 | postgres | UNICODE
(4 rows)
List of relations
Name | Type | Owner
-----------+-------+-------
bib_lt | table | ennio
bib_lt1 | table | ennio
bibdbt_lt | table | ennio
bibl_bug | table | ennio
bidbt | table | ennio
bidbt2 | table | ennio
pippo | table | ennio
pro_chars | table | ennio
(8 rows)
-------------------
Could somebody explain what's going on and where I'm wrong?
Thanks for your attention.
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]
Try psql mydb < /home/ennio/db.out instead.
If something fails while postgres is trying to change the encoding from
sql-ascii to Latin1 then there will be no data in the table(s) affected.
Since you are going from 7.2 to 7.4 you might need to restore the 7.2 as
7.4 sql-ascii first. Then dump new 7.4 sql-ascii version and re-import
specifying Latin-1.
Mike
On Thu, 2004-07-08 at 08:57, Ennio-Sr wrote:
> Hi all!
> Something weird seems to be happening when I try to restore a db,
> pg_dumped on PC 1, on a second PC.
> PG vers. 7.2 is running on both PCs (under Debian/Woody, k 2.2.22).
> Here follow some of the steps taken:
>
> On PC no. 1:
> ===========
> postgres$ pg_dump mydb > /home/ennio/db.out
>
> then I ftped db.out to PC no. 2
> -------------
> On PC no.2:
> ==========
> postgres$ pg_restore -C -d mydb /home/ennio/db.out
>
> pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL 1: Database "mydb" does not exist in the
systemcatalog.
> ----------
> I connect as user ennio and create mydb:
> => create database mydb with encoding='SQL-ASCII';
> (I have to discover why it would not accept 'LATIN1' ...)
> ----------
> => \l
> List of databases
> Name | Owner | Encoding
> -------------+----------+-----------
> mydb | ennio | SQL_ASCII
> odontolinux | postgres | UNICODE
> template0 | postgres | UNICODE
> template1 | postgres | UNICODE
> (4 rows)
>
>
> After creating mydb I try again:
> pg_restore -C /home/ennio/db.out >>draft1 2>&1
>
> finally obtaining:
>
> quote:
> -----------------------------------------------------------------------
> pg_restore: [archiver] WARNING:
> Data restoration may fail because existing triggers cannot be disabled
> (no superuser user name specified). This is only a problem when
> restoring into a database with already existing triggers.
> --
> -- Selected TOC Entries:
> --
> \connect - ennio
>
> --
> -- TOC Entry ID 1 (OID 0)
> --
> -- Name: mydb Type: DATABASE Owner: ennio
> -- Data Pos: 0 (Length 0)
> --
>
> CREATE DATABASE "mydb" WITH TEMPLATE = template0 ENCODING = 7;
>
> \connect mydb ennio
>
> --
> -- TOC Entry ID 2 (OID 47191)
> --
> -- Name: bibl_bug Type: TABLE Owner: ennio
> -- Data Pos: 0 (Length 0)
> --
>
> CREATE TABLE "bibl_bug" (
> "autore" character varying,
> "titolo" character varying,
> "altre_notizie" character varying,
> "collana" character varying
> );
>
> [ cut ]
>
> COPY "bibl_bug" FROM stdin;
> TOUTAIN J. L'économie antique [Pr. acq. FB 377, pc. FB 400] L'Évolution de l'Humanité. Synthèse Collect.
Dirigéepar Henri Beer
> ZWIRNER Giuseppe Istituzioni di Matematiche per gli studenti delle facoltà di chimica, agraria, scienze naturali,
economiae commercio e statistica. Parte prima: rist. dell'ottava ediz. riveduta ed ampliata con numerosi esercizi e
problemirisolti e proposti. 1973. Parte sec. 4^ ed.
> \.
> [ .............. cut .................]
>
> --
> -- Data for TOC Entry ID 67 (OID 53440)
> --
> -- Name: pro_chars Type: TABLE DATA Owner: ennio
> -- Data Pos: 22728 (Length 67)
> --
>
>
> COPY "pro_chars" FROM stdin;
> ALT+176 °
> ALT+177 ±
> ALT+178 ²
> ALT+171 «
> ALT+187 »
> ALT+233 é
> ALT+177 ±
> \.
>
> , paranormal , 0 , , VAUTOER Robert , I poteri magici della luna
, , Della Valle Editore. Torino, 1971. , Torino , 1971 , La Sfinge. 1. Direttore Stefano
Jacini. , 1 , , 228 , ril. ed. cop. fig. , 11.5 , 19.0 , 2500.00 ,
2500.00 , 0.00 , Siena , gen. 1987 , Bassi , , F , 0.00 ,
, \r
> \.
>
>
> --
> -- Data for TOC Entry ID 69 (OID 57620)
> --
> -- Name: bib_lt Type: TABLE DATA Owner: ennio
> -- Data Pos: 56530 (Length 31974)
> --
>
>
> COPY "bib_lt" FROM stdin;
> 0 varie 0 SERAFINI Maria Teresa Come si studia Con 6 pp. di catalogo
Bompiani.Milano, novembre 1989 Milano 1989 Strumenti Bompiani. Collana diretta da Umberto Eco 1
341 br. pl. 12.5 19.2 21600.00 24000.00 24000.00 Siena giu. 2000 Feltrinell
F 0.00
> [ ................ cut ..................]
>
> paranormal 0 VAUTOER Robert I poteri magici della luna Della Valle Editore.
Torino,1971. Torino 1971 La Sfinge. 1. Direttore Stefano Jacini. 1 228 ril. ed. cop. fig.
11.5 19.0 2500.00 2500.00 0.00 Siena gen. 1987 Bassi F 0.00
> \.
>
>
> --
> -- Data for TOC Entry ID 70 (OID 59443)
> --
> -- Name: bibdbt_lt Type: TABLE DATA Owner: ennio
> -- Data Pos: 88504 (Length 2975)
> --
>
>
> COPY "bibdbt_lt" FROM stdin;
> [ ........... cut ............]
> \.
>
>
> --
> -- Data for TOC Entry ID 71 (OID 61155)
> --
> -- Name: pippo Type: TABLE DATA Owner: ennio
> -- Data Pos: 91479 (Length 412)
> --
>
>
> COPY "pippo" FROM stdin;
> [ ,,,,,,,,,,, cut ...............]
> PGBCOPY\n¿\r\n \t
> \N
> \N
> \.
>
> --
> -- Data for TOC Entry ID 74 (OID 62123)
> --
> -- Name: pga_queries Type: TABLE DATA Owner: ennio
> -- Data Pos: 96135 (Length 271)
> --
>
>
> COPY "pga_queries" FROM stdin;
> ricerca_per_argomento S select * from bib_lt where argomento like '%[parameter "argomento?"]%'
\n
>
> [ ................ cut .............]
> \.
>
>
> --
> -- Data for TOC Entry ID 75 (OID 62128)
> --
> -- Name: pga_forms Type: TABLE DATA Owner: ennio
> -- Data Pos: 96406 (Length 27)
> --
>
>
> COPY "pga_forms" FROM stdin;
> \.
>
>
> --
> -- Data for TOC Entry ID 76 (OID 62133)
> --
> -- Name: pga_scripts Type: TABLE DATA Owner: ennio
> -- Data Pos: 96433 (Length 27)
> --
>
>
> COPY "pga_scripts" FROM stdin;
> \.
>
>
> --
> -- Data for TOC Entry ID 77 (OID 62138)
> --
> -- Name: pga_reports Type: TABLE DATA Owner: ennio
> -- Data Pos: 96460 (Length 27)
> --
>
>
> COPY "pga_reports" FROM stdin;
> \.
>
>
> --
> -- Data for TOC Entry ID 78 (OID 62143)
> --
> -- Name: pga_schema Type: TABLE DATA Owner: ennio
> -- Data Pos: 96487 (Length 27)
> --
>
>
> COPY "pga_schema" FROM stdin;
> \.
>
> [ cut ]
> ------------------------------------------------------------------
> unquote
>
> Howeer, when I connect (ennio$ psql mydb) and do a
> select * from any of the tables listed anove;
> the structure of the fields only appears, with no data on them!:
>
> => select * from bib_lt;
>
> n_prog | argomento | class_dewey | collocaz | autore | titolo | altre_notizie | editore | citta | data_pubbl |
collana| voll | n_pag_pref | n_pag_testo | car_tec_ed | largh_cm | alt_cm | pr_acq_lit | pr_listino | pr_copert |
luogo_acq| data_acq | libraro | note | scheda_ltr | val_merc | data_e_fonte | dummy
>
--------+-----------+-------------+----------+--------+--------+---------------+---------+-------+------------+---------+------+------------+-------------+------------+----------+--------+------------+------------+-----------+-----------+----------+---------+------+------------+----------+--------------+-------
> (0 rows)
>
> List of databases
> Name | Owner | Encoding
> -------------+----------+-----------
> mydb | ennio | SQL_ASCII
> odontolinux | postgres | UNICODE
> template0 | postgres | UNICODE
> template1 | postgres | UNICODE
> (4 rows)
>
> List of relations
> Name | Type | Owner
> -----------+-------+-------
> bib_lt | table | ennio
> bib_lt1 | table | ennio
> bibdbt_lt | table | ennio
> bibl_bug | table | ennio
> bidbt | table | ennio
> bidbt2 | table | ennio
> pippo | table | ennio
> pro_chars | table | ennio
> (8 rows)
>
> -------------------
>
> Could somebody explain what's going on and where I'm wrong?
> Thanks for your attention.
> Ennio.
>
* Ennio-Sr <nasr.laili@tin.it> [080704, 15:57]:
> Hi all!
> Something weird seems to be happening when I try to restore a db,
> pg_dumped on PC 1, on a second PC.
> [ ... cut ...]
Finally I got my data on the 2nd PC :-)
Unfortunately, I'm not able to state how, as it could depend on many
changes I made. May be experts among you can throw some light on the
issue:
As I said on my previous post (answering Mike, non yet on the list) I
tried a combination of commands with no success.
Then rebooted (PC no. 2) choosing kernel 2.4.18 and tried the same
commands again: sometimes I got the tables structures, sometimes not
even these and always no rows with data ...
After that I looked for regression test but could not find the relative
package (I read that postgresql-test conflicts with the 7.2 currently
running but was unable to find where the 7.2 compatible version is!).
So I decided to install all missing packages, i.e. postgresql-dev and
postgresql-contrib: no chance to get the pg_regress but, when as
ordinary user I did:
$ pg_restore -d mydb db.out
I got back my data, finally :-)))
Now, the point is:
. is it possible that k 2.2.22 was responsible for that strange
behaviour?
. or it was related to some libs which were installed with pg-dev and
pg-contrib?
One further note:
When I was trying invain all suggested commands (taken from PG
Documentation or suggestions on Google) I could see a lot of stuff (my
data) passing on the screen during command execution: a look into the file
I was sometime redirecting the output to (see below) shows that the
'copy' command was being given and, apparently, executed, although non
rows where in effect being added to the tables:
> -----------------------------------------------
> -- Data for TOC Entry ID 67 (OID 53440)
> --
> -- Name: pro_chars Type: TABLE DATA Owner: ennio
> -- Data Pos: 22728 (Length 67)
> --
> COPY "pro_chars" FROM stdin;
> ALT+176 °
> ALT+177 ±
> ALT+178 ²
> ALT+171 «
> ALT+187 »
> ALT+233 é
> ALT+177 ±
> \.
> ------------------------------------------------
Whereas when I gave the successful command (with no re-directing, of
course) nothing appeared on the screen, except the warning
> -----------------------------------------------------------------------
> pg_restore: [archiver] WARNING:
> Data restoration may fail because existing triggers cannot be disabled
> (no superuser user name specified). This is only a problem when
> restoring into a database with already existing triggers.
> --------------------------------------
Thanks for the attention,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]