Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group