Home > mailing lists

Thread: 2 versions running & using pg_dumpall

2 versions running & using pg_dumpall

From

Ralph Smith

Date:

25 October 2007, 16:50:51

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

=====================

Re: 2 versions running & using pg_dumpall

From

Tom Lane

Date:

25 October 2007, 17:13:13

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

Re: 2 versions running & using pg_dumpall

From

Ralph Smith

Date:

25 October 2007, 17:44:58

==============================

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

=====================

Re: 2 versions running & using pg_dumpall

From

Tom Lane

Date:

25 October 2007, 19:24:18

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

Re: 2 versions running & using pg_dumpall

From

Ralph Smith

Date:

25 October 2007, 19:48:28

On Oct 25, 2007, at 12:24 PM, Tom Lane wrote:

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

==============================================

postgres=# \l

List of databases

Name | Owner | Encoding

-----------+----------+-----------

airburst | root | SQL_ASCII

postgres | postgres | UTF8

template0 | postgres | UTF8

template1 | postgres | UTF8

(4 rows)

postgres=# select * from pg_database ;

-----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+-------------------------------------

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)

From 7.4's postgresql.conf in /etc/postgresql/7.4/main:

#---------------------------------------------------------------------------

# CONNECTIONS AND AUTHENTICATION

#---------------------------------------------------------------------------

# - Connection Settings -

tcpip_socket = true

max_connections = 100

# note: increasing max_connections costs about 500 bytes of shared

# memory per connection slot, in addition to costs from shared_buffers

# and max_locks_per_transaction.

#superuser_reserved_connections = 2

port = 5433

unix_socket_directory = '/var/run/postgresql'

#unix_socket_group = ''

#unix_socket_permissions = 0777 # octal

#virtual_host = '' # what interface to listen on; defaults to any

From 8.2's postgresql.conf in /etc/postgresql/8.2/main:

#---------------------------------------------------------------------------

# CONNECTIONS AND AUTHENTICATION

#---------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost' # what IP address(es) to listen on;

# comma-separated list of addresses;

# defaults to 'localhost', '*' = all

# (change requires restart)

port = 5432 # (change requires restart)

I certainly can use some help!

Ralph Smith

smithrn@u.washington.edu

=====================

Re: 2 versions running & using pg_dumpall

From

Tom Lane

Date:

25 October 2007, 20:09:36

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

Re: 2 versions running & using pg_dumpall

From

Ralph Smith

Date:

25 October 2007, 20:32:33

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 ;

-----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+-------------------------------------

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

=====================

Re: 2 versions running & using pg_dumpall

From

Tom Lane

Date:

25 October 2007, 20:57:46

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

Re: 2 versions running & using pg_dumpall

From

Ralph Smith

Date:

25 October 2007, 22:36:51

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
=====================

Re: 2 versions running & using pg_dumpall

From

Devrim GÜNDÜZ

Date:

25 October 2007, 22:46:49

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/

Attachment

signature.asc

Re: 2 versions running & using pg_dumpall

From

Ralph Smith

Date:

25 October 2007, 23:00:12

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 ;

-----------+--------+----------+---------------+--------------+---------------+--------------+--------------+---------+-----------+--------------------------

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

=====================

[ANN]VTD-XML 2.2

From

"jimmy Zhang"

Date:

26 October 2007, 01:31:53

XimpleWare is proud to announce the the release of version 2.2 of VTD-XML, the next generation XML parsers/indexer/slicer/editor. This release significantly expands VTD-XML's ability to slice, split, edit and incrementally update the XML documents. To this end, we introduce the concept of namespace-compensated element fragment. This release also adds VTD+XML index writing capability to the VTD Navigator class. Other enhancements in this release include index size pre-computation, support for HTTP get, and some bug fixes.

To download the latest release, please go to http://sourceforge.net/project/showfiles.php?group_id=110612.