I have versions 7.4 (port=5433) & 8.2 (port=5432) on this Ubuntu box.

I want to use v8.2's pg_dumpall to export v7.4's data into a text file.
(IDEALLY I'd like to port it directly to psql and avoid the file, but
I don't know if I can do that.)

Anyway, when I:
prompt:/usr/lib/postgresql/8.2/bin$ ./pg_dumpall -c -v -p 5433 >
myfile.txt

It's NOT dumping everything. Only... Well, I'm not sure.
I think only DB postgres. It's only 87 lines long.

Anybody have any suggestions?

Thank you,
Ralph Smith
smithrn@u.washington.edu
=====================

Ralph Smith <smithrn@u.washington.edu> writes:
> I want to use v8.2's pg_dumpall to export v7.4's data into a text file.

> prompt:/usr/lib/postgresql/8.2/bin$ ./pg_dumpall -c -v -p 5433 >
> myfile.txt

Works for me. What does the -v give you on stderr? Also,
7.4.what-exactly and 8.2.what-exactly?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

==============================
On Oct 25, 2007, at 10:13 AM, Tom Lane wrote:

> Ralph Smith <smithrn@u.washington.edu> writes:
>> I want to use v8.2's pg_dumpall to export v7.4's data into a text
>> file.
>
>> prompt:/usr/lib/postgresql/8.2/bin$ ./pg_dumpall -c -v -p 5433 >
>> myfile.txt
>
> Works for me. What does the -v give you on stderr? Also,
> 7.4.what-exactly and 8.2.what-exactly?
>
> regards, tom lane
==============================
Sorry for the bulk here...
-------------------------------
postgres@smithrn-ltb1:/usr/lib/postgresql/8.2/bin$ ./pg_dumpall -c -v
-p 5433pg_dumpall: executing SET search_path = pg_catalog
--
-- PostgreSQL database cluster dump
--

-- Started on 2007-10-25 10:40:28 PDT

\connect postgres

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET escape_string_warning = 'off';

pg_dumpall: executing SELECT usename as rolname, usesuper as
rolsuper, true as rolinherit, usesuper as rolcreaterole, usecreatedb
as rolcreatedb, usecatupd as rolcatupdate, true as rolcanlogin, -1 as
rolconnlimit, passwd as rolpassword, valuntil as rolvaliduntil, null
as rolcomment FROM pg_shadow UNION ALL SELECT groname as rolname,
false as rolsuper, true as rolinherit, false as rolcreaterole, false
as rolcreatedb, false as rolcatupdate, false as rolcanlogin, -1 as
rolconnlimit, null::text as rolpassword, null::abstime as
rolvaliduntil, null as rolcomment FROM pg_group WHERE NOT EXISTS
(SELECT 1 FROM pg_shadow WHERE usename = groname) ORDER BY 1
--
-- Roles
--

DROP ROLE postgres;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN;
pg_dumpall: executing SELECT useconfig[1] FROM pg_shadow WHERE
usename = 'postgres'


pg_dumpall: executing SELECT groname, grolist FROM pg_group ORDER BY 1


--
-- Database creation
--

pg_dumpall: executing SELECT datname, coalesce(usename, (select
usename from pg_shadow where usesysid=(select datdba from pg_database
where datname='template0'))), pg_encoding_to_char(d.encoding),
datistemplate, datacl, -1 as datconnlimit, 'pg_default' AS
dattablespace FROM pg_database d LEFT JOIN pg_shadow u ON (datdba =
usesysid) WHERE datallowconn ORDER BY 1
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT CREATE,TEMPORARY ON DATABASE template1 TO postgres WITH GRANT
OPTION;
pg_dumpall: executing SELECT datconfig[1] FROM pg_database WHERE
datname = 'template1';


pg_dumpall: executing SELECT datname FROM pg_database WHERE
datallowconn ORDER BY 1
pg_dumpall: dumping database "template1"...
\connect template1

pg_dumpall: running ""/usr/lib/postgresql/8.2/bin/pg_dump" -v -p
'5433' -Fp 'template1'"
pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined conversions
pg_dump: reading user-defined tables
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: reading type casts
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
--
-- PostgreSQL database dump
--

-- Started on 2007-10-25 10:40:28 PDT

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

pg_dump: creating COMMENT DATABASE template1
--
-- TOC entry 1352 (class 0 OID 0)
-- Dependencies: 1351
-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON DATABASE template1 IS 'Default template database';


pg_dump: creating SCHEMA public
pg_dump: creating COMMENT SCHEMA public
--
-- TOC entry 1353 (class 0 OID 0)
-- Dependencies: 4
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


pg_dump: setting owner and privileges for COMMENT DATABASE template1
pg_dump: setting owner and privileges for SCHEMA public
pg_dump: setting owner and privileges for COMMENT SCHEMA public
pg_dump: setting owner and privileges for ACL public
--
-- TOC entry 1354 (class 0 OID 0)
-- Dependencies: 4
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2007-10-25 10:40:28 PDT

--
-- PostgreSQL database dump complete
--

-- Completed on 2007-10-25 10:40:28 PDT

--
-- PostgreSQL database cluster dump complete
--



Ralph Smith
smithrn@u.washington.edu
=====================

Ralph Smith <smithrn@u.washington.edu> writes:
> On Oct 25, 2007, at 10:13 AM, Tom Lane wrote:
>> Works for me. What does the -v give you on stderr? Also,
>> 7.4.what-exactly and 8.2.what-exactly?

> Sorry for the bulk here...

Hmph. Nothing obviously wrong there, except that it's not finding
anything except template1. What does "select * from pg_database"
show?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Ralph Smith <smithrn@u.washington.edu> writes:
> On Oct 25, 2007, at 12:24 PM, Tom Lane wrote:
>> Hmph. Nothing obviously wrong there, except that it's not finding
>> anything except template1. What does "select * from pg_database"
>> show?

> postgres=# select * from pg_database ;
> datname | datdba | encoding | datistemplate | datallowconn |
> datconnlimit | datlastsysoid | datfrozenxid | dattablespace |
> datconfig | datacl
> -----------+--------+----------+---------------+--------------
> +--------------+---------------+--------------+---------------
> +-----------+-------------------------------------

Ummm ... those are the column headings for 8.2 pg_database, not 7.4.
You're looking at the wrong postmaster.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Oct 25, 2007, at 1:09 PM, Tom Lane wrote:

> Ralph Smith <smithrn@u.washington.edu> writes:
>> On Oct 25, 2007, at 12:24 PM, Tom Lane wrote:
>>> Hmph. Nothing obviously wrong there, except that it's not finding
>>> anything except template1. What does "select * from pg_database"
>>> show?
>
>> postgres=# select * from pg_database ;
>> datname | datdba | encoding | datistemplate | datallowconn |
>> datconnlimit | datlastsysoid | datfrozenxid | dattablespace |
>> datconfig | datacl
>> -----------+--------+----------+---------------+--------------
>> +--------------+---------------+--------------+---------------
>> +-----------+-------------------------------------
>
> Ummm ... those are the column headings for 8.2 pg_database, not 7.4.
> You're looking at the wrong postmaster.
>
> regards, tom lane
===============================================
Oops. And I thought I got over NOT using the full path to the
commands...
Sorry about that.

postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ ./psql
Welcome to psql 7.4.13, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# select * from pg_database ;
datname | datdba | encoding | datistemplate | datallowconn |
datconnlimit | datlastsysoid | datfrozenxid | dattablespace |
datconfig | datacl
-----------+--------+----------+---------------+--------------
+--------------+---------------+--------------+---------------
+-----------+-------------------------------------
postgres | 10 | 6 | f | t
| -1 | 10818 | 524 | 1663
| |
template1 | 10 | 6 | t | t
| -1 | 10818 | 524 | 1663
| | {=c/postgres,postgres=CTc/postgres}
template0 | 10 | 6 | t | f
| -1 | 10818 | 524 | 1663
| | {=c/postgres,postgres=CTc/postgres}
airburst | 17032 | 0 | f | t
| -1 | 10818 | 524 | 1663
| |
(4 rows)

Ralph Smith
smithrn@u.washington.edu
=====================

Ralph Smith <smithrn@u.washington.edu> writes:
> On Oct 25, 2007, at 1:09 PM, Tom Lane wrote:
>> Ummm ... those are the column headings for 8.2 pg_database, not 7.4.
>> You're looking at the wrong postmaster.

> postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ ./psql
> Welcome to psql 7.4.13, the PostgreSQL interactive terminal.

> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit

> postgres=# select * from pg_database ;
> datname | datdba | encoding | datistemplate | datallowconn |
> datconnlimit | datlastsysoid | datfrozenxid | dattablespace |
> datconfig | datacl

Still the wrong column headings :-(. What you have above is a 7.4 psql
connecting to an 8.2 postmaster, no doubt because the default port
number wired into it is the 8.2 installation's not the 7.4 one's.
You'll need to explicitly give a -p switch to psql to connect to the
correct postmaster.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Oct 25, 2007, at 1:57 PM, Tom Lane wrote:

> Ralph Smith <smithrn@u.washington.edu> writes:
>> On Oct 25, 2007, at 1:09 PM, Tom Lane wrote:
>>> Ummm ... those are the column headings for 8.2 pg_database, not 7.4.
>>> You're looking at the wrong postmaster.
>
>> postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ ./psql
>> Welcome to psql 7.4.13, the PostgreSQL interactive terminal.
>
>> Type: \copyright for distribution terms
>> \h for help with SQL commands
>> \? for help on internal slash commands
>> \g or terminate with semicolon to execute query
>> \q to quit
>
>> postgres=# select * from pg_database ;
>> datname | datdba | encoding | datistemplate | datallowconn |
>> datconnlimit | datlastsysoid | datfrozenxid | dattablespace |
>> datconfig | datacl
>
> Still the wrong column headings :-(. What you have above is a 7.4
> psql
> connecting to an 8.2 postmaster, no doubt because the default port
> number wired into it is the 8.2 installation's not the 7.4 one's.
> You'll need to explicitly give a -p switch to psql to connect to the
> correct postmaster.
>
> regards, tom lane
===========================================
postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ ./psql -p 5433
psql: FATAL: database "postgres" does not exist

looks like I have to re-install PG 7.4. There's only only 41 MB there.

On Ubuntu, what are my options?
Use Synaptic to uninstall?

Once that's done, any caveats WRT the subsequent install?

All this is so I can practice and make mistakes here on test boxes
before w move the real on from 7.4 to 8.2.

Thanks a bunch Tom.

Ralph Smith
smithrn@u.washington.edu
=====================


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hi,

On Thu, 2007-10-25 at 15:36 -0700, Ralph Smith wrote:
> postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ ./psql -p 5433
> psql: FATAL: database "postgres" does not exist

7.4 does not have postgres database. use ./psql template1 -p 5433.

--
Devrim GÃœNDÃœZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)

iD8DBQBHIRyYpme12CBXnxERAtR/AKCLDE8dPdALJbWIji7h5aKBqD823wCfWqsd
MhzKC2zRJcJdY8hDBNAEomk=
=JlnA
-----END PGP SIGNATURE-----

On Oct 25, 2007, at 3:45 PM, Devrim GÜNDÜZ wrote:

> ./psql template1 -p 5433

=================================================
postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ ./psql -p 5433
template1
Welcome to psql 7.4.13, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=# \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
template0 | postgres | UNICODE
template1 | postgres | UNICODE
(2 rows)

template1=# select * from pg_database ;
datname | datdba | encoding | datistemplate | datallowconn |
datlastsysoid | datvacuumxid | datfrozenxid | datpath | datconfig
| datacl
-----------+--------+----------+---------------+--------------
+---------------+--------------+--------------+---------+-----------
+--------------------------
template1 | 1 | 6 | t | t
| 17140 | 7251 | 3221232724 | | |
{postgres=C*T*/postgres}
template0 | 1 | 6 | t | f
| 17140 | 464 | 464 | | |
{postgres=C*T*/postgres}
(2 rows)

Whoooo hoooo!
Now we're back to square one.

I can re-make postgres on v7.4 (already done)
go back to my old dumpall and use that 7.4 dumpall to load PG 7.4

Remove the test DB from 8.2 as the dumpall that loaded it was via 7.4
not 8.2
use 8.2's dumpall to dump the 7.4
use 8.2's psql to load in that dump


Ralph Smith
smithrn@u.washington.edu
=====================