Re: [GENERAL] 2 versions running & using pg_dumpall

From: Ralph Smith <smithrn(at)u.washington.edu>
Date: Thu Oct 25 2007 - 13:44:48 EDT

---

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

> Ralph Smith  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

---

Received on Thu Oct 25 13:49:29 2007