How to...?
Bruce...
Did I miss something here? I just looked through the migration
file for 6.2.1->6.3, and it doesn't seem to say, but how do you dump
the data from a v6.2.1 database and then reload it to a v6.3 one?
Using v6.2.1's pg_dump/pg_dumpall, I did:
pg_dumpall -o > db.dump
That worked.
Then, I installed v6.3, and using its psql, I did:
psql -e < db.dump
That failed miserably.
First thing that failed was building the new pg_user...so I cut
out that and did it manually using createuser...
Then, using what was left, I did:
psql -e < db.dump
Again. Failed misearbly, with the following coming from the 'copy
to <relname> from stdin;' section:
344984 johnb xgSldZdYEgIWo clio.trends.ca n \N
\? -- help
\a -- toggle field-alignment (currenty on)
\C [<captn>] -- set html3 caption (currently '')
\connect <dbname|-> <user> -- connect to new database (currently
'acctng')
\copy table {from | to} <fname>
\d [<table>] -- list tables and indices, columns in <table>, or * for all
\da -- list aggregates
\dd [<object>]- list comment for table, field, type, function, or
operator.
\df -- list functions
\di -- list only indices
\do -- list operators
\ds -- list only sequences
\dS -- list system tables and indexes
\dt -- list only tables
\dT -- list types
\e [<fname>] -- edit the current query buffer or <fname>
\E [<fname>] -- edit the current query buffer or <fname>, and execute
\f [<sep>] -- change field separater (currently '|')
\g [<fname>] [|<cmd>] -- send query to backend [and results in <fname> or
pipe]
\h [<cmd>] -- help on syntax of sql commands, * for all commands
\H -- toggle html3 output (currently off)
\i <fname> -- read and execute queries from filename
\l -- list all databases
\m -- toggle monitor-like table display (currently off)
\o [<fname>] [|<cmd>] -- send all query results to stdout, <fname>, or
pipe
\p -- print the current query buffer
\q -- quit
\r -- reset(clear) the query buffer
\s [<fname>] -- print history or save it in <fname>
\t -- toggle table headings and row count (currently on)
\T [<html>] -- set html3.0 <table ...> options (currently '')
\x -- toggle expanded output (currently off)
\z -- list current grant/revoke permissions
\! [<cmd>] -- shell escape or command
344985 bonnies x/lgef4ULWJv2 clio.trends.ca n \N
\? -- help
\a -- toggle field-alignment (currenty on)
\C [<captn>] -- set html3 caption (currently '')
\connect <dbname|-> <user> -- connect to new database (currently
'acctng')
\copy table {from | to} <fname>
\d [<table>] -- list tables and indices, columns in <table>, or * for all
\da -- list aggregates
\dd [<object>]- list comment for table, field, type, function, or
operator.
So, is there, like, a trick to this? *raised eyebrow* Have I
missed something important here?
Thanks...
Bruce...
Did I miss something here? I just looked through the migration
file for 6.2.1->6.3, and it doesn't seem to say, but how do you dump
the data from a v6.2.1 database and then reload it to a v6.3 one?Using v6.2.1's pg_dump/pg_dumpall, I did:
pg_dumpall -o > db.dump
Yes.
That worked.
Then, I installed v6.3, and using its psql, I did:
psql -e < db.dump
That failed miserably.
OK.
First thing that failed was building the new pg_user...so I cut
out that and did it manually using createuser...Then, using what was left, I did:
psql -e < db.dump
Again. Failed misearbly, with the following coming from the 'copy
to <relname> from stdin;' section:344984 johnb xgSldZdYEgIWo clio.trends.ca n \N
Check what is in the file around this line. It has existed the COPY for
some reason, and the \N is triggering the \? output.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
Then, using what was left, I did:
psql -e < db.dump
Again. Failed misearbly, with the following coming from the 'copy
to <relname> from stdin;' section:344984 johnb xgSldZdYEgIWo clio.trends.ca n \N
I got something kind-of similar with a core dump... This was 'cos of a
column name which is now a reserved word.
Andrew
----------------------------------------------------------------------------
Dr. Andrew C.R. Martin University College London
EMAIL: (Work) martin@biochem.ucl.ac.uk (Home) andrew@stagleys.demon.co.uk
URL: http://www.biochem.ucl.ac.uk/~martin
Tel: (Work) +44(0)171 419 3890 (Home) +44(0)1372 275775
Import Notes
Resolved by subject fallback
On Mon, 9 Mar 1998, Andrew Martin wrote:
Then, using what was left, I did:
psql -e < db.dump
Again. Failed misearbly, with the following coming from the 'copy
to <relname> from stdin;' section:344984 johnb xgSldZdYEgIWo clio.trends.ca n \N
I got something kind-of similar with a core dump... This was 'cos of a
column name which is now a reserved word.
Ack, I fear you are correct...the third field above is 'password',
which became a reserved word in v6.3...oh man, is this upgrade ever going
to hurt...my 'db.dump' file is 84Meg...vi just loves it :)
On Mon, 9 Mar 1998, Andrew Martin wrote:
Then, using what was left, I did:
psql -e < db.dump
Again. Failed misearbly, with the following coming from the 'copy
to <relname> from stdin;' section:344984 johnb xgSldZdYEgIWo clio.trends.ca n \N
I got something kind-of similar with a core dump... This was 'cos of a
column name which is now a reserved word.Ack, I fear you are correct...the third field above is 'password',
which became a reserved word in v6.3...oh man, is this upgrade ever going
to hurt...my 'db.dump' file is 84Meg...vi just loves it :)
I do think it would be a good idea if the pg_dumpall from the new release
looked for these now-reserved column names and warned the user. You could
then at least know where problems will occur. In upgrades from d/b versions
which support ALTER TABLE xxx RENAME COLUMN yyy TO zzz, you would actually
be able to do something about it before re-dumping...
Andrew
----------------------------------------------------------------------------
Dr. Andrew C.R. Martin University College London
EMAIL: (Work) martin@biochem.ucl.ac.uk (Home) andrew@stagleys.demon.co.uk
URL: http://www.biochem.ucl.ac.uk/~martin
Tel: (Work) +44(0)171 419 3890 (Home) +44(0)1372 275775
Import Notes
Resolved by subject fallback
I do think it would be a good idea if the pg_dumpall from the new release
looked for these now-reserved column names and warned the user. You could
then at least know where problems will occur. In upgrades from d/b versions
which support ALTER TABLE xxx RENAME COLUMN yyy TO zzz, you would actually
be able to do something about it before re-dumping...
pg_dumpall is just a shell script. You would have to do it in pg_dump.
I hesistate to meddle with that code unless I have good reason for it.
We could have a separate function that scans pg_attribute looking for
bad column names.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
On Mon, 9 Mar 1998, The Hermit Hacker wrote:
On Mon, 9 Mar 1998, Andrew Martin wrote:
Then, using what was left, I did:
psql -e < db.dump
Again. Failed misearbly, with the following coming from the 'copy
to <relname> from stdin;' section:344984 johnb xgSldZdYEgIWo clio.trends.ca n \N
I got something kind-of similar with a core dump... This was 'cos of a
column name which is now a reserved word.Ack, I fear you are correct...the third field above is 'password',
which became a reserved word in v6.3...oh man, is this upgrade ever going
to hurt...my 'db.dump' file is 84Meg...vi just loves it :)
How about starting up your old pgsql, then psql, the 'alter table rename
column.....'
Maarten
_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------
On Mon, 9 Mar 1998, Maarten Boekhold wrote:
On Mon, 9 Mar 1998, The Hermit Hacker wrote:
On Mon, 9 Mar 1998, Andrew Martin wrote:
Then, using what was left, I did:
psql -e < db.dump
Again. Failed misearbly, with the following coming from the 'copy
to <relname> from stdin;' section:344984 johnb xgSldZdYEgIWo clio.trends.ca n \N
I got something kind-of similar with a core dump... This was 'cos of a
column name which is now a reserved word.Ack, I fear you are correct...the third field above is 'password',
which became a reserved word in v6.3...oh man, is this upgrade ever going
to hurt...my 'db.dump' file is 84Meg...vi just loves it :)How about starting up your old pgsql, then psql, the 'alter table rename
column.....'
Cause I keep forgetting that I can do that :( Point taken and
will try that out, thanks...
Ack, I fear you are correct...the third field above is 'password',
which became a reserved word in v6.3...oh man, is this upgrade ever going
to hurt...my 'db.dump' file is 84Meg...vi just loves it :)How about starting up your old pgsql, then psql, the 'alter table rename
column.....'
I assume normal users do the pg_dump, delete their databases and old
binaries, then try to reload into 6.3.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)I do think it would be a good idea if the pg_dumpall from the new release
looked for these now-reserved column names and warned the user. You could
then at least know where problems will occur. In upgrades from d/b versions
which support ALTER TABLE xxx RENAME COLUMN yyy TO zzz, you would actually
be able to do something about it before re-dumping...pg_dumpall is just a shell script. You would have to do it in pg_dump.
I hesistate to meddle with that code unless I have good reason for it.
Having psql core dump seems like a pretty good reason to me :-)
We could have a separate function that scans pg_attribute looking for
bad column names.
Whatever... This could be run as the first stage of pg_dumpall to say
"Hey these column names are no longer allowed..."
Bruce Momjian | 830 Blythe Avenue
Andrew
----------------------------------------------------------------------------
Dr. Andrew C.R. Martin University College London
EMAIL: (Work) martin@biochem.ucl.ac.uk (Home) andrew@stagleys.demon.co.uk
URL: http://www.biochem.ucl.ac.uk/~martin
Tel: (Work) +44(0)171 419 3890 (Home) +44(0)1372 275775
Import Notes
Resolved by subject fallback
I do think it would be a good idea if the pg_dumpall from the new release
looked for these now-reserved column names and warned the user. You could
then at least know where problems will occur. In upgrades from d/b versions
which support ALTER TABLE xxx RENAME COLUMN yyy TO zzz, you would actually
be able to do something about it before re-dumping...pg_dumpall is just a shell script. You would have to do it in pg_dump.
I hesistate to meddle with that code unless I have good reason for it.Having psql core dump seems like a pretty good reason to me :-)
Tough to argue with this.
We could have a separate function that scans pg_attribute looking for
bad column names.Whatever... This could be run as the first stage of pg_dumpall to say
"Hey these column names are no longer allowed..."Bruce Momjian | 830 Blythe Avenue
Andrew
----------------------------------------------------------------------------
Dr. Andrew C.R. Martin University College London
EMAIL: (Work) martin@biochem.ucl.ac.uk (Home) andrew@stagleys.demon.co.uk
URL: http://www.biochem.ucl.ac.uk/~martin
Tel: (Work) +44(0)171 419 3890 (Home) +44(0)1372 275775
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
Ack, I fear you are correct...the third field above is 'password',
which became a reserved word in v6.3...oh man, is this upgrade ever going
to hurt...my 'db.dump' file is 84Meg...vi just loves it :)
Well, this doesn't solve the general problem, but "password" can be used as a
column name without inducing shift/reduce conflicts. I'll patch the source
tree sometime soon; in the meantime add the obvious source around line 4618
in gram.y:
| PASSWORD { $$ = "password"; }
(add tabs to taste :)
- Tom