Re: [LONG] Need help on pg_dump!

From: mike g <mike(at)thegodshalls(dot)com>
To: nasr(dot)laili(at)tin(dot)it
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [LONG] Need help on pg_dump!
Date: 2004-07-09 06:01:39
Message-ID: 1089352899.7310.17.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 system catalog.
> ----------
> 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. Synthse Collect. Dirige par Henri Beer
> ZWIRNER Giuseppe Istituzioni di Matematiche per gli studenti delle facolt di chimica, agraria, scienze naturali, economia e commercio e statistica. Parte prima: rist. dell'ottava ediz. riveduta ed ampliata con numerosi esercizi e problemi risolti 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.
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Oliver Elphick 2004-07-09 08:41:12 Re: array in postgre
Previous Message Steve Crawford 2004-07-08 16:41:46 Re: Storage space lost during an UPDATE