Re: backup script

On 05/11/2012 12:38 PM, Vo, Catherine CTR DTIC Z wrote:
> We are using Sun Solaris 64bits version 10.
> postgresql-9.1.3-S10.sparc-6
>
> I downloaded the script from some of the website and try to test out but
> didn't work at all. Don't know how to create a backup user (I know the
> syntax but where should I create it?) Normall, we goes to sqlplus / as
> sysdba on oracle and mysql -u root -p on mysql, but where should I
> create it on postgres?
The answer (as always) is, "it depends". I'll try to get you pointed in
the right direction.

The tools you need are called pg_dump and/or pg_dumpall:
http://www.postgresql.org/docs/current/static/app-pgdump.html
http://www.postgresql.org/docs/current/static/app-pg-dumpall.html

The tools connect to the database pretty much like any other PostgreSQL
client connects (user/password/dbname/etc.) and must have permissions
appropriate to access the data being dumped. This does not necessarily
need to be a superuser. If Pat owns his/her database and wants to dump
it s/he can.

To dump everything in the database cluster (user/role information, all
databases, ...) requires superuser privilege. The PostgreSQL superuser
is "postgres".

For any connection to the database, whether general user or for doing
dumps, you need to make sure the connection is allowed in pg_hba.conf:
http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

> Also, when I start the postgres, I saw 5 processes running... this is
> odd isn't it?

Not odd at all. There is a main process listening for connections plus
several helper processes doing things like managing statistics
collection, managing database vacuuming (maintenance including
dead-space reclamation through autovacuum) and so on. There will also be
a process for every connected user.
> Beside that, don't see port running on /tmp directory.
>

That is a default but can be changed a build-time or through configuration:
http://www.postgresql.org/docs/current/static/runtime-config-connection.html

If your database is running and you can connect, try:
show unix_socket_directory;

Cheers,
Steve