Postgres restore

[Doug Easterbrook]

hi chris.

also be a aware that there is a difference bewteen. pg_dump and pg_dumpall


pg_dump lets you extract a single database from the database server.  eg on the command line (both mac and windows)

pg_dump -F c -v database > pathnameWhereToSaveDatabase.  -or-

pg_dump -F c -v MyDatabase > C:\folder1\folder2\MyDatabase.backup


do a        pg_dump  —help.        to see the list of parameters.  there are others that you might want to use depending on where the database is.

-U username
-H hostIP





pg_dumpall.   dumps ALL databases in the database cluster and may not be what you want.   it seems like you references that  in your original post.


finally.   

 if you  use the -F c flags on a pg_dump, you need to restore using pg_restore.  This is because the backup has some compression in it

if you leave those flags out of the pg_dump command line, it makes a raw SQL dump that is quite large and you need to restore using psql. — which processes raw sql.


hint, you can load up a new table into an existing database by executing a load of the data  from psql






how do you get your omnis program to do database backups?   I would not.   I would use a cron job or a windows task to do it on a frequent basis.





if you want to see both mac and windows scripts, I’ve got an example that I share with people at

http://www2.artsman.com/Software/Version10/BackupScript.zip


these scripts:
backup the database
delete old copies (and retain a month end one)
will FTP the data to another machine

and they are well commented


Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On May 18, 2020, at 6:10 AM, Chris Hughes <ataddata at bigpond.net.au> wrote:
> 
> Thanks for your reply, where does command line go ??
> 
> 
> 
> -----Original Message-----
> From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf
> Of Phil (OmnisList) via omnisdev-en
> Sent: Monday, 18 May 2020 9:14 PM
> To: omnisdev-en at lists.omnis-dev.com
> Cc: Phil (OmnisList)
> Subject: Re: Postgres restore
> 
> Hi Chris,
> 
> pg_restore is a command line program, not a sql statement...
> as is pg_dump et al.
> 
> regards
> Phil Potter
> Based in Chester in the UK.
> 
> On 18/05/2020 11:58, Chris Hughes wrote:
>> Hi Gentleman
>> 
>> Am on the right track to a restore from a dump all backup.
>> 
>> 
>> 
>> Do method $GetUserName Returns LvUserName
>> 
>> Do method $GetHost Returns LvHostName
>> 
>> Do method $GetPort Returns LvPort
>> 
>> Calculate LvDataBaseCurrent as tSessionObj.$database
>> 
>> 
>> 
>> ;
>> 
>> If tLoginState=kTrue
>> 
>>                 Do tSessionObj.$newstatementref Returns LvStatementObjRef
>> 
>>                 If not(LvStatementObjRef.$validref)     ;; Null test on
>> object.
>> 
>>                 Breakpoint {bug trap}
>> 
>>                 End If
>> 
>>                 Begin statement
>> 
>>                 ;
>> 
>>                 Sta: SELECT pg_restore -U [LvUserName] -h [LvHostName] -v
> -d
>> [LvDataBaseCurrent] -p [LvPort] -f [pBackUpFile] ;
>> 
>> 
>> 
>>                 End statement
>> 
>>                 Get statement LvSQL
>> 
>> ;
>> 
>>                 Do LvStatementObj.Ref$execdirect([LvSQL]) Returns LvError
>> ;; LvStatementObj
>> 
>>                 If LvStatementObjRef.$errorcode
>> 
>>                                 OK message Select Failed (Icon) {$Restore
>> [LvStatementObjRef.$nativeerrortext] [LvStatementObj.$errorcode]}
>> 
>>                 End If
>> 
>>                 Do LvStatementObjRef.$fetch(LvResultsRow)
>> 
>>                 If LvStatementObjRef.$errorcode
>> 
>>                                 OK message Select Failed (Icon) {$Restore
>> [LvStatementObjRef.$nativeerrortext]}
>> 
>>                 Calculate LvFlag as kFalse
>> 
>>                 Else
>> 
>>                                 Do LvStatementObjRef.$deleteref
>> 
>>                                 Quit method LvResultsRow.c1
>> 
>>                                 Calculate LvFlag as kTrue
>> 
>>                 End If
>> 
>> End If
>> 
>> Quit method LvFlag
>> 
>> 
>> 
>> 
>> 
>> Chris Hughes
>> 
>> 
>> 
>> Copious Data Pty Ltd (Trading as Atad Data)
>> 
>> 3 Springwell Avenue
>> 
>> Springwood QLD 4127
>> 
>> Phone No:- 07 3208 6347
>> 
>> Mobile No:- 0409 263 349
>> 
>> Email:- ataddata at bigpond.net.au
>> 
>> Web Site:- http://ataddata.com.au
>> 
>> 
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com