Thread: getting info out of a dump from postgresql

--- Barry Gribben <barry.gribben@cbg.co.nz> wrote:
> Hi there, one of our clients has supplied a database
> as a 3MB "dump" from
> their database provider. The first few characters of
> the file are: --

Sounds like you are starting from scratch.  If you are
only interested in the data, it is in there somewhere,
probably in the form of tab-delimited tables.

But you probably want to recreate the database.  Basic
steps:

1. Set up a working installation of PostgreSQL, if you
don't already have one.  You don't say what operating
system your unix machine is running, but more than
likely there is a package available which will
streamline the installation for you.  If you need more
information, post more information about your setup.

2. Make sure you have users set up for your PostgreSQL
installation.  If you have installed from a package,
most likely a PostgreSQL superuser has already been
created (probably named "postgres").  To create any
other users, connect to the database:
psql -U pgsuperuser template1
then do:
\h create user
which will tell you what you need to know.  Hint:
create a user having the same name as your OS user
name.
Also try:
\h create database
You will want to create a new database to load the
dump into.  Log out by doing:
\q

3. Load the dump.  Edit out all of the lines starting
with "\connect" (these assume that you have users by
those names, which is probably not the case).  Then
do:
psql -U pgsuperuser -f dumpfile databasename

4. Assuming all goes well, connect to the database:
psql databasename
then:
\d
which will give you a list of all of the database
objects.  Also:
\dt
which will give you a list of tables.  Then:
\d tablename
for a list of columns etc.
From here you can issue SQL commands.
\? will give you a list of psql commands.

5. Get the pgsql ODBC driver:
http://gborg.postgresql.org

This is a gross simplification of the process.  If you
hit a snag or need more information, just post details
of what you did and what happened as a result, and
someone will help you.

>
> -- Selected TOC Entries:
>
> --
>
> \connect - postgres
>
>
>
> --
>
> -- TOC Entry ID 103 (OID 16556)
>
> --
>
> -- Name: "plpgsql_call_handler" () Type: FUNCTION
> Owner: postgres
>
> --
>
>
>
> CREATE FUNCTION "plpgsql_call_handler" () RETURNS
> opaque AS
> '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE
> 'C';
>
>
>
> --
>
> -- TOC Entry ID 104 (OID 16557)
>
> --
>
> -- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner:
>
> --
>
>
>
> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
> "plpgsql_call_handler"
> LANCOMPILER '';
>
>
>
> \connect - root
>
>
>
> --
>
> -- TOC Entry ID 2 (OID 61967)
>
> --
>
> -- Name: staff_id_seq Type: SEQUENCE Owner: root
>
> --
>
>
>
> CREATE SEQUENCE "staff_id_seq" start 1 increment 1
> maxvalue
> 9223372036854775807 minvalue 1 cache 1;
>
>
>
> --
>
> -- TOC Entry ID 4 (OID 61969)
>
> --
>
> -- Name: client_id_seq Type: SEQUENCE Owner: root
>
> --
>
>
>
> CREATE SEQUENCE "client_id_seq" start 1 increment 1
> maxvalue
> 9223372036854775807 minvalue 1 cache 1;
>
>
>
> --
>
> -- TOC Entry ID 6 (OID 61971)
>
> --
>
> -- Name: question_group_id_seq Type: SEQUENCE Owner:
> root
>
> --
>
>
>
> CREATE SEQUENCE "question_group_id_seq" start 1
> increment 1 maxvalue
> 9223372036854775807 minvalue 1 cache 1;
>
>
>
> --
>
> -- TOC Entry ID 8 (OID 61973)
>
> --
>
> -- Name: question_
>
>
>
> My question is naturally, how do I turn this into
> data I can ODBC to (or any
> other access method) from SAS, which I run under
> windows. I have a unix
> machine on our network, but any options considered.
>
>
>
> Many thanks for any help.
>
>
>
> Dr Barry Gribben
>
> MBChB BA MMedSci FRNZCGP
>
> Medical Director
>
> CBG Health Research Limited
>
> www.cbg.co.nz <http://www.cbg.co.nz/>
>
>
>
>




__________________________________
Do you Yahoo!?
Vote for the stars of Yahoo!'s next ad campaign!
http://advision.webevents.yahoo.com/yahoo/votelifeengine/