Results 1 to 9 of 9

Thread: Urgent Help

  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: Urgent Help

    All

    Need some help regarding a postgres database that I have inherited, I am on oracle DBA with absolutely no postgres experience , I have been told to restore a postgres database but I have not got a clue. its sitting on a linux server.

    what i need to know is how do i log into the database , check for datafiles etc and if i can someone locate the backup restore it.

    anyway can point me to a dummy guide or give me some pointers I would be extremely grateful.

    thanks
    Mike

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by mdesouza
    what i need to know is how do i log into the database
    You do not need to "log in" to restore a dump, although you will be required to provide a username and password when restoring a dump
    The SQL*Plus equivalent for PG is psql.
    Details are here

    check for datafiles etc
    Not sure what the equivalent for datafiles would be. I don't think PG has a similar concept. Propably Postgres' tablespaces come close to that.
    All data for what you would call an instance in Oracle, is stored in a directory that is refered to as a "database cluster.
    There should either be an environment variable (on the server) called PGDATA that points to that directory or it is supplied on the commandline when starting the server.
    Details are here

    and if i can someone locate the backup restore it.
    Not sure what you mean with "someone". The equivalent to Oracle's exp/imp is pg_dump or pg_dumpall
    Details are here

    anyway can point me to a dummy guide or give me some pointers I would be extremely grateful
    All the documentation is online:
    http://www.postgresql.org/docs/8.2/static/index.html

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    thanks shammat
    Cannot believe that no one know anything about this database , I did some digging around and saw the follwing processes
    ***********************
    postgres 18762 1 0 Apr15 ? 00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
    postgres 18764 18762 0 Apr15 ? 00:00:00 postgres: logger process
    postgres 18766 18762 0 Apr15 ? 00:00:19 postgres: writer process
    postgres 18767 18762 0 Apr15 ? 00:00:00 postgres: stats collector process
    postgres 23548 18762 0 Jul06 ? 00:00:00 postgres: lm lyris [local] idle
    postgres 23549 18762 0 Jul06 ? 00:00:00 postgres: lm lyris [local] idle

    So I guess the instance is started and the username is postgres, so i su as postgres

    what next I need to do is find out if there is a database? Is there a easy way of finding out?Welcome to psql 8.2.3, the PostgreSQL interactive terminal.

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

    postgres=#

    thanks

    mike
    I have logged in as psql

  4. #4
    Join Date
    Aug 2007
    Posts
    3

    hi

    shammat

    I also did the following
    -bash-3.00$ cat pg_database

    "postgres" 103020 1663 524
    "lyris" 10819 1663 524
    "template1" 1 1663 524
    "template0" 10818 1663 524

    So i am assuming there are two databases postgres and lyris , and the rest are just templates to create databases

    thanks

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by mdesouza
    postgres 18762 1 0 Apr15 ? 00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
    This means that you have a running postgres instance, and all the database files ("Database Cluster") are located in the directory /var/lib/pgsql/data
    I have logged in as psql
    Not sure what you mean with "as psql". I would have assumed that the (db) user is postgres.
    I also did the following
    -bash-3.00$ cat pg_database
    You should really go through the documentation links I provided. To list all databases, enter \l (that's an lower-case L) at the psql prompt.

    Also check out the manual for the explanation on what those template databases are.

  6. #6
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    here is a pretty simple backup/restore procedure that I can show you to give you an idea how how it works. DO NOT just try this as part of it involves dumping the database (ill use lyris in the example since that seems to be the db of relevance)

    you should sudo over to a administrator, like postgres, to do this.

    do the backup: (I would recommend something like this before attempting a restore)
    Code:
    $pg_dump -Fc -d lyris -f backupfile.bak
    the -Fc can be found in the notes.. its basically a custom format for backing up.

    now to do a restore of the db, I just drop the existing one (if it exists) then create a new one.

    Code:
    $dropdb lyris
    
    $createdb -e --encoding UNICODE --owner lyrisdbowner lyris
    you can check the roles available from psql using \du. Also you should check who owns lyris by doing \l+ from psql.

    last I do the restore after the db is created again:

    Code:
    $pg_restore -Fc -d lyris < backupfile.bak
    Once again, I am not saying you should do this.. I am just trying to give a quick demo of one way that it can be done. I think you need to learn how to do all this and understand it.

    And as a bit of unwanted advice.. I would recommend telling whoever gave you the assignment that programmers/admins are not interchangeable parts that they can fiddle with blindly

    Anyone have any good links on how to do backups? I am actually interested in this after running into some unexpected behavior in the past

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    There is a good administration program that comes bundled with the windows version where you can login to the linux box from a windows machine. Look on postgres' website for pgadminIII. You will be able to do your backups and restores with this program. You need to know however that you will have to slightly tweak the postgres config file to accept connections because it is set up to deny all connection out of the box on a linux distro.

  8. #8
    Join Date
    Sep 2007
    Posts
    1
    fjm1967 is right.... look at there..

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by staafoz
    fjm1967 is right.... look at there..
    It sure feels good to be right for a change.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •