Обсуждение: backup and restore

hi all
I am developing web site use database is PostGres and now i must build
function backup and Restore database on Web
My web develop on framework 2.0(asp.net 2.0)
I know have two file on forder bin use backup and restore is: pg_restore.exe
, pg_dump.exe
I have plan is write function or procedures on Database can backup and
restore database.
How can i write this function ???
Some body show for me?
--
View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10389799
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 09.05.2007 09:45, anhtin wrote:
> hi all
> I am developing web site use database is PostGres and now i must build
> function backup and Restore database on Web
> My web develop on framework 2.0(asp.net 2.0)
> I know have two file on forder bin use backup and restore is: pg_restore.exe
> , pg_dump.exe
> I have plan is write function or procedures on Database can backup and
> restore database.
> How can i write this function ???
> Some body show for me?

Well, a good start might be to take a look at the manual of the
application in question?

See http://www.postgresql.org/docs/current/static/backup.html

--
Regards,
Hannes Dorbath

thanks reply for me
but have propblem:
the function pg_start_backup()
i dont run this function
Can u send me example is a procedure or function can backup and restore for
me ????

--
View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10391372
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Anybody show for me ?????
i want backup database and i read on Internet have function
pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup)
but i not run
Some body show me. How will i do run this function ??
--
View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10426698
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Anybody show for me ?????
i want backup database and i read on Internet have function
pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup)
but i not run
Some body show me. How will i do run this function ??
--
View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10426702
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Anybody show for me ?????
i want backup database and i read on Internet have function
pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup)
but i not run
Some body show me. How will i do run this function ??
--
View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10426704
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

anhtin wrote:
> Anybody show for me ?????
> i want backup database and i read on Internet have function
> pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup)
> but i not run
> Some body show me. How will i do run this function ??

See:

http://www.postgresql.org/docs/8.2/static/continuous-archiving.html

--
Paul Lambert
Database Administrator
AutoLedgers

anhtin wrote:
> Anybody show for me ?????
> i want backup database and i read on Internet have function
> pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup)
> but i not run
> Some body show me. How will i do run this function ??

Read the documentation!

http://www.postgresql.org/docs/current/static/backup-dump.html


pg_start_backup is NOT for creating full backups - it is for creating
partial backups (as you would see here:
http://www.postgresql.org/docs/8.2/static/continuous-archiving.html).

--
Postgresql & php tutorials
http://www.designmagick.com/

Thank reply for me
Can u show me
How will i backup database on Function or proc on Database??
--
View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10427265
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 14 jan, 10:36, hiddenhippo <reda...@gmail.com> wrote:
> Having recently jumped ship from MSSQL to Postgres I'm finding it a
> little difficult getting onto the 'correct path.'  Basically I have a
> live database and I want to take a copy of that and then restore it
> onto a backup server.
>
> Firstly I'm using Postgres 8.2 and pgAdmin 1.6.3
>
> Within the pgAdmin interface I've selected the database I wish to
> backup, given it a filename, selected 'Compress' and have chosen blobs
> and OIDs.  From the documentation it appears that if you use foreign
> keys then you should use OIDs.  Anyway, the backup appears to work
> fine and a file is created.  No errors are displayed at any point.
>
> When I come to restore the database I fistly create a blank one, and
> then right click and choose restore.  The problem is that errors are
> thrown because of foreign-key constraints.
>
> With a little more investigation I changed the backup output to
> 'plain' so that I could see the script generated.  It appears that the
> backup process is created tables and their associated data in the
> wrong order.  For example,  at the top of the generated file it
> attempts to insert data into a given table, however the table dictates
> that some entries must have a corresponding entry in another, for
> example a user id (a foreign key saying that UID value must exist on
> the user tables primary key).  After detailing the data for the first
> table it then details the data that should go into the parent table,
> or in my example above, it then attempts to populate the user table.
> When you run the restore process this it fails because the user table
> doesn't have the corresponding entries.
>
> Could someone please suggest why this is happening and how, if it can
> be done, I fix it?
>
> Thanks

It's quite simple to have problems with pgAdmin's backup procedure.
Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could
report many errors when did backups and/or restores from it.

I suggest you to use pg_dump command line utility into a plain SQL
file, so you can compress it with bzip2 to maximum compress rates.
When you restore, decompress the file (if you have packed it) and use
the psql to load the script file into the new and clean database.

T.J. Adami wrote:
> On 14 jan, 10:36, hiddenhippo <reda...@gmail.com> wrote:
>> Having recently jumped ship from MSSQL to Postgres I'm finding it a
>> little difficult getting onto the 'correct path.'  Basically I have a
>> live database and I want to take a copy of that and then restore it
>> onto a backup server.
>>
>> Firstly I'm using Postgres 8.2 and pgAdmin 1.6.3

OK. Latest is 8.2.6 I believe, make sure you keep up to date with minor
updates.
Oh, and pgAdmin has released version 1.8 now, although that shouldn't
affect backups.

>> Within the pgAdmin interface I've selected the database I wish to
>> backup, given it a filename, selected 'Compress' and have chosen blobs
>> and OIDs.  From the documentation it appears that if you use foreign
>> keys then you should use OIDs.

You probably want "compress" - that format allows you do partial
restores as well as being compressed. I can't tell whether you've got
any blobs ("large objects" in PG terminology). I don't think you'll need
to dump OIDs unless you're explicitly using them. The manuals have
recommended against that for some years now, so if you can remember
which bit gave you the impression you need them for foreign keys then
that would be useful.

 >  Anyway, the backup appears to work
>> fine and a file is created.  No errors are displayed at any point.

Excellent. You should also have a "messages" tab that shows the pg_dump
command it uses. You can run the same from the command-line to dump your
database. If you've got "verbose messages" turned on then it will name
each item as it dumps it too. I'm working from pgAdmin 1.8 here, but I
don't think it was any different in 1.6

>> When I come to restore the database I fistly create a blank one, and
>> then right click and choose restore.  The problem is that errors are
>> thrown because of foreign-key constraints.

OK, that's down to one of three things:
1. The database wasn't empty - perhaps you had a partial restore
in-place. Unlikely, since I'm sure you repeated your steps above.

2. The dump was in plain-text and you were doing separate schema + data
restores. We can rule this out because you've said otherwise.

3. You aren't restoring as a super-user (typically "postgres"), so it
can't disable + re-enable foreign keys. That's what I think is happening.

>> With a little more investigation I changed the backup output to
>> 'plain' so that I could see the script generated.  It appears that the
>> backup process is created tables and their associated data in the
>> wrong order.  For example,  at the top of the generated file it
>> attempts to insert data into a given table, however the table dictates
>> that some entries must have a corresponding entry in another, for
>> example a user id (a foreign key saying that UID value must exist on
>> the user tables primary key).  After detailing the data for the first
>> table it then details the data that should go into the parent table,
>> or in my example above, it then attempts to populate the user table.
>> When you run the restore process this it fails because the user table
>> doesn't have the corresponding entries.

If you look more carefully, you should see that all the foreign keys are
created at the bottom of the file after the data restore.

>> Could someone please suggest why this is happening and how, if it can
>> be done, I fix it?
>>
>> Thanks
>
> It's quite simple to have problems with pgAdmin's backup procedure.

Hmm - shouldn't be, and if so then please let the pgAdmin people know!
They're always working to improve the package and it's

> Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could
> report many errors when did backups and/or restores from it.

Did you keep any notes, and can you reproduce them?

> I suggest you to use pg_dump command line utility into a plain SQL
> file, so you can compress it with bzip2 to maximum compress rates.
> When you restore, decompress the file (if you have packed it) and use
> the psql to load the script file into the new and clean database.

You're better off using the compressed mode imnsho. The flexibility is
well worth any minor loss vs bzip2's compression. Check out the -l / -L
options to let you pick and choose what you restore.

--
   Richard Huxton
   Archonet Ltd

On 15 jan, 12:29, d...@archonet.com (Richard Huxton) wrote:
> > It's quite simple to have problems with pgAdmin's backup procedure.
>
> Hmm - shouldn't be, and if so then please let the pgAdmin people know!
> They're always working to improve the package and it's
>

I think the same. We (company I work for) develop an ERP software with
more than 200 customers, and each one have a individual and
confidential database with PostgreSQL.
Using pgAdmin version 1.6.x or lower with those databases causes
errors when the database is restored from a .backup file created with
compression from pgAdmin. However, to kill the doubt, I realize those
errors with pg_restore utility from terminal as well.

> > Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could
> > report many errors when did backups and/or restores from it.
>
> Did you keep any notes, and can you reproduce them?

In fact, I do not keep notes from those errors because they're always
on database schema. The table and its data can be restored very well.
So we have a utility written in Visual Foxpro wich can read postgres
database schema and do the database diff to upgrade the restored db
with the schema needed (PKs, FKs, views, triggers, etc).
I will study these cases most extensively in our labs, and will take
notes to post them here (once I can prove it's not a pgAdmin problem
but pg_dump issue when using compressing - if not proved, I send it to
pgAdmin developers.).

> > I suggest you to use pg_dump command line utility into a plain SQL
> > file, so you can compress it with bzip2 to maximum compress rates.
> > When you restore, decompress the file (if you have packed it) and use
> > the psql to load the script file into the new and clean database.
>
> You're better off using the compressed mode imnsho. The flexibility is
> well worth any minor loss vs bzip2's compression. Check out the -l / -L
> options to let you pick and choose what you restore.

The only way I found to dump and restore databases with no errors is
using pg_dump creating a plain text file. Compression is a good way,
but I prefer create a plain text SQL file and compress it my way
after...
And maybe bzip2 couldn't be the best pratice, it was just a
suggestion ;)

P.S: I have to trace exactly these pgAdmin issues. The issues really
exists (I'll not post any fake message here if it was not true), but I
can't send databases dump to reproduce them according the contract
with our customers (confidential data).

shall be sprinkled on his
garments, so as to stain all his raiment. He will not only hate you, but
he will have you in the utmost contempt: no place shall be thought fit
for you, but under his feet to be trodden down as the mire of the
streets.


The misery you are exposed to is that which God will inflict to that
end, that he might show what that wrath of Jehovah is. God hath had it
on his heart to show to angels and men, both how excellent his love is,
and also how terrible his wrath is. Sometimes earthly kings have a mind
to show how terrible their wrath is, by the extreme punishments they
would execute on those that would provoke them. Nebuchadnezzar, that
mighty and haughty monarch of the Chaldean empire, was willing to show
his wrath when enraged with Shadrach, Meshach, and Abednego; and
accordingly gave orders that the burning fiery furnace should be heated
seven times hotter than it was before; doubtless, it was raised to the
utmost degree of fierceness that human art could raise it. But the great
God is also willing to show his wrath, and magnify his awful majesty and
mighty power in the extreme sufferings of his enemies. Rom. 9:22. "What
if God, willing to show his wrath, and to make his power known, endured
with much long-suffering the vessels of wrath fitted to destruction?"
And seeing this is his design, and what he has determined, even to show
how terrible the unrestrained wrath, the fury and fierceness of Jehovah
is, he will do it to effect. There will be something accomplished and
brought to pass that will be dreadful with a witness. When the great and
angry God hath risen up and executed his awful vengeance on the poor
sinner, and the wretch is actually su

the others in
their vastness. For who will not be astounded at the fact that our body,
which a little while ago was imperceptible in the universe, itself
imperceptible in the bosom of the whole, is now a colossus, a world, or
rather a whole, in respect of the nothingness which we cannot reach? He who
regards himself in this light will be afraid of himself, and observing
himself sustained in the body given him by nature between those two abysses
of the Infinite and Nothing, will tremble at the sight of these marvels; and
I think that, as his curiosity changes into admiration, he will be more
disposed to contemplate them in silence than to examine them with
presumption.

For, in fact, what is man in nature? A Nothing in comparison with the
Infinite, an All in comparison with the Nothing, a mean between nothing and
everything. Since he is infinitely removed from comprehending the extremes,
the end of things and their beginning are hopelessly hidden from him in an
impenetrable secret; he is equally incapable of seeing the Nothing from
which he was made, and the Infinite in which he is swallowed up.

What will he do then, but perceive the appearance of the middle of things,
in an eternal despair of knowing either their beginning or their end. All
things proceed from the Nothing, and are borne towards the Infinite. Who
will follow these marvellous processes? The Author of these wonders
understands them. None other can do so.

Through failure to contemplate these Infinites, men have rashly rushed into
the examination of nature, as though they bore some proportion to her. It is
strange that they have wished to understand the beginnings of things, and
thence to arrive at the knowledge of the whole, with a presumption as
infinite as their object. For surely this design cannot be formed without
presumption or without a ca

T.J. Adami wrote:
> On 15 jan, 12:29, d...@archonet.com (Richard Huxton) wrote:
>>> It's quite simple to have problems with pgAdmin's backup procedure.
>> Hmm - shouldn't be, and if so then please let the pgAdmin people know!
>> They're always working to improve the package and it's
>>
>
> I think the same. We (company I work for) develop an ERP software with
> more than 200 customers, and each one have a individual and
> confidential database with PostgreSQL.
> Using pgAdmin version 1.6.x or lower with those databases causes
> errors when the database is restored from a .backup file created with
> compression from pgAdmin. However, to kill the doubt, I realize those
> errors with pg_restore utility from terminal as well.

What puzzles me is that as far as I know, pgAdmin *uses* pg_dump and
pg_restore to handle its backup needs.

> P.S: I have to trace exactly these pgAdmin issues. The issues really
> exists (I'll not post any fake message here if it was not true), but I
> can't send databases dump to reproduce them according the contract
> with our customers (confidential data).

I'm sure that will be appreciated. Open source projects rely on
knowledgable users who are prepared to do a little investigating. It's
what helps drive reliability up.

--
   Richard Huxton
   Archonet Ltd

On 16/01/2008, Richard Huxton <dev@archonet.com> wrote:
> T.J. Adami wrote:
> What puzzles me is that as far as I know, pgAdmin *uses* pg_dump and
> pg_restore to handle its backup needs.

It does. Make sure it's using the correct version for your server
though - that's the only reason it might fail that I can think of. In
1.6 it'll use the first version it finds. In 1.8, you can select the
path yourself if you like.

Regards, Dave