Re: [LONG] Need help on pg_dump! - Mailing list 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
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.
>