Postgres Pro
Downloads
Home   >   mailing lists

Thread: Backups

Backups

From
Eduardo Vázquez Rodríguez
Date:
Hello everybody out there using Postgres!!!

I have a couple of questions

1. If I am not wrong (using default values) Postgresql allocate all the
database tables under /usr/local/data. is that correct?

2. How do I make a backup of a database in a different partition?, for
instance /home/postgres

Thanks in advanced

Return Value on UPDATE trigger

From
derrick@grifflink.com
Date:
On a table update, I want to check for certain conditions in a trigger
function, and if a condition is met, NOT ALLOW the update.  Not a big
problem...I can accomplish that.

My question is this:  How can I send a message back to the calling
application telling the user that the update was denied or failed, and for
the user to try again?  Basically, I need a way to send a return value back
to the application (a 0 or a 1) when a trigger function says so.

Any ideas?

Thanks,
Derrick

Re: Return Value on UPDATE trigger

From
derrick@grifflink.com
Date:
RAISE EXCEPTION 'Your specific error message';
    works just great.

----- Original Message -----
From: <derrick@grifflink.com>
To: <pgsql-novice@postgresql.org>
Sent: Monday, September 20, 2004 1:09 PM
Subject: [NOVICE] Return Value on UPDATE trigger


> On a table update, I want to check for certain conditions in a trigger
> function, and if a condition is met, NOT ALLOW the update.  Not a big
> problem...I can accomplish that.
>
> My question is this:  How can I send a message back to the calling
> application telling the user that the update was denied or failed, and for
> the user to try again?  Basically, I need a way to send a return value
back
> to the application (a 0 or a 1) when a trigger function says so.
>
> Any ideas?
>
> Thanks,
> Derrick
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

Re: Backups

From
Frank Bax
Date:
At 02:41 PM 9/20/04, Eduardo Vázquez Rodríguez wrote:
>How do I make a backup of a database in a different partition?, for
>instance /home/postgres


This works for me (replace [database] with name of your database):

pg_dump -o -c [database] | gzip > /home/postgres/[database].`date
+\%Y\%m\%d`.gz

Frank

Re: Backups

From
Neacal
Date:
In article <414F2471.9050104@insys-corp.com.mx>, Eduardo Vázquez
Rodríguez <evazquez@insys-corp.com.mx> wrote:

> Hello everybody out there using Postgres!!!
>
> I have a couple of questions
>
> 1. If I am not wrong (using default values) Postgresql allocate all the
> database tables under /usr/local/data. is that correct?
>
> 2. How do I make a backup of a database in a different partition?, for
> instance /home/postgres
>
> Thanks in advanced
>

Hola Eduardo

I'm using the MacOSX package, which suggests your q1 should be revised
to /usr/local/pgsql/data.

I'm still a novice to postgresql, but as I have just researched the
backup issue in the O'Reilly book, I'll have a go at answering q2.

pg_dump will do this for you; you don't have to be in the pgsql
directory, pg_dump can even be run from another server.

My best guess is to run the command from the other partition. So it
would be something like:

# pathto/usr/local/bin/pg_dump --file=youdata.sql --format=p yourdata

you have a choice of formats:  c=gzip, t=tar, p=plain text

If you wanted to backup the entire filesystem, stop the server and make
a copy, something like this (compressed):

# cd /usr/local/pgsql
# /usr/local/bin/pg_ctl stop
# tar czf pgsql.bak.tar.gz data/

then you can

# mv pgsql.bak.tar.gz /path/to/where/you/want

or cp to be safe!

Well, that's my solution. I did try out various options myself on a
testdb, with no problems. So I hope I'm not misleading you.

Buena suerte

Neacal