Re: [GENERAL] How to implement backup protocol
You seem dead set on making your life harder than it needs to be. If you really don't want to have the ssh port open then set the backup to run from a cron-job on the main server and have it scp the result over to the backup server. That way only the backup server needs ssh open. By backup clients use cheap ADSL internet connections. Thery have all ports closed by ISP. How to force postmaster to run the tool which system provides when it receives backup request instead of postgres child process ? Don't. You're just making life difficult for yourself. Use the standard Unix solution for such things - ssh. How are you installing updates without ssh access to the server? My servers are usually Windows computers. I have installed LogMein ( www.logmein.com ) which allows to access those computers when ports are closed at both sides. I havent installed any updates. I start with 8.1 and installed last version on new server. So some servers have 8.1.1, some 8.1.2 etc up to 8.1.5 I havent touched old servers after system is set up. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to implement backup protocol
Not sure but using a binary cursor might improve things. Why not use COPY protocol? I did full text search in Postgres 8.1 help file for COPY protocol but havent found any matches. Which is COPY protocol and how to use it ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement backup protocol
http://www.postgresql.org/files/documentation/books/aw_pgsql/node210.html just a simple command to copy data to and from tables/files. Mark Jensen - Original Message From: Andrus [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Wednesday, November 29, 2006 2:43:56 PM Subject: Re: [GENERAL] How to implement backup protocol Not sure but using a binary cursor might improve things. Why not use COPY protocol? I did full text search in Postgres 8.1 help file for COPY protocol but havent found any matches. Which is COPY protocol and how to use it ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement backup protocol
Andrus wrote: You seem dead set on making your life harder than it needs to be. If you really don't want to have the ssh port open then set the backup to run from a cron-job on the main server and have it scp the result over to the backup server. That way only the backup server needs ssh open. By backup clients use cheap ADSL internet connections. Thery have all ports closed by ISP. In that case, if you really can't have incoming ports enabled at either end, you'll need to have a separate machine available to both servers and clients. Then you can use that as a switch and have: 1. servers connect and upload backups to it 2. clients connect and download the last backup. How to force postmaster to run the tool which system provides when it receives backup request instead of postgres child process ? Don't. You're just making life difficult for yourself. Use the standard Unix solution for such things - ssh. How are you installing updates without ssh access to the server? My servers are usually Windows computers. I have installed LogMein ( www.logmein.com ) which allows to access those computers when ports are closed at both sides. Hmm - looks like they're doing something similar to the process I describe above, then running ftp/vnc etc. over the link. OK, so do something with the logmein client instead of ssh then. Assuming you can script it. It seems to suggest that you can do file synchronisation across their connection. I havent installed any updates. I start with 8.1 and installed last version on new server. So some servers have 8.1.1, some 8.1.2 etc up to 8.1.5 I havent touched old servers after system is set up. You should try and come up with a way of upgrading the servers to the latest (8.1.5 at the moment) on a regular basis. Some of those upgrades are very important. Use your logmein setup to keep them up to date. If you have many servers you'll want to automate the process, but I'm afraid I don't do enough Windows work to make any useful suggestions there. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement backup protocol
The weekly backup of the larger of the two databases produces a file that is about 20GB and takes about an hour and 15 minutes. I then compress it down to about 4 GB, which takes another hour. However, because that's a separate task, it doesn't impact the database server as much. (I suspect all that I/O slows things down a bit, but I haven't noticed any significant effect in my transaction time reports. That task is run during the slowest 4 hour period of the week, though). My environment is a bit different. For safety, I need to create backups to separate computer over over internet. 1. Backup computer has client category internet connection (ADSL, 600 KBit/s download speed). 2. Query SELECT sum( relpages * 8/1000) FROM pg_class returns 1302 for a database restored from backup. So my data size seems to be approx 1 GB only. 3. Backup file size is 70 M 4. Backup client has all ports closed. 5. Server has *only* 5432 port open. So I think that 4.5 hours which requires to create backup is because pg_dump download the whole database (1 GB) in uncompressed format over slow internet connection. Compression level does not affect to this almost at all. I think I can create backup copy fast in server computer but how to send it to backup computer? pg_read_file() can read only text files and is restricted only to superusers. How to add a function pg_read_backup() to Postgres which creates and returns backup file with download speed ? This problably requires implementing some file download protocol. BTW, if you've never actually tested your recovery capabilities, can you be sure they work? I did a full-blown test in February or March and found a few loose ends. And when we had to do the real thing in May (due to a power supply failure), there were STILL a few loose ends, but we were back online within 12 hours of when I started the recovery process, and half of that time was spent completing the setup of the 'backup' server, which I had been rebuilding. I'm working to lower that downtime and will be doing another full-blown test in January or February. I expect that full database backup created using pd_dump does not have never have any problems on restore. Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to implement backup protocol
On Tue, Nov 28, 2006 at 06:01:43PM +0200, Andrus wrote: 5. Server has *only* 5432 port open. pg_read_file() can read only text files and is restricted only to superusers. How to add a function pg_read_backup() to Postgres which creates and returns backup file with download speed ? You could use an *un*trusted procedural language to create a function to binary-read the backup from disk and return it as a bytea field. Not sure how efficient that is, though. You could then simply do select get_backup(); If you allow for parameters you could make it return certain backups based on, perhaps, timestamp of creation. select list_available_backups(); might complete the suite of tools. One could then always use some hashing tools (mhash with PG bindings comes to mind) to verify whether a backup has arrived safely: on local machine: ripemd160(backupfile) select yhash.ripemd160(get_backup()) = local hash; Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to implement backup protocol
Andrus wrote: So I think that 4.5 hours which requires to create backup is because pg_dump download the whole database (1 GB) in uncompressed format over slow internet connection. Compression level does not affect to this almost at all. Might well be latency issues too. I think I can create backup copy fast in server computer but how to send it to backup computer? Use scp. Open port 22 and allow only connections from the backup machine with a specified user (e.g. pgbackup). Alternatively, you might try dumping in a text-format and using rsync to transfer changes. pg_read_file() can read only text files and is restricted only to superusers. How to add a function pg_read_backup() to Postgres which creates and returns backup file with download speed ? This problably requires implementing some file download protocol. Just don't try and do it within PG - use the tools the system provides. I expect that full database backup created using pd_dump does not have never have any problems on restore. It's entirely possible to cause problems by restoring as the wrong user or with a different character-set / locale. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to implement backup protocol
You could use an *un*trusted procedural language to create a function to binary-read the backup from disk and return it as a bytea field. Not sure how efficient that is, though. You could then simply do select get_backup(); If you allow for parameters you could make it return certain backups based on, perhaps, timestamp of creation. Karsten, This id good idea but it forces to use Postgres protocol for downloading. This protocol has some timeouts which are too small for large file download. Postgres protocol has also a lot of overhead added to downloadable data. It also requires that whole downloadable file must fit into memory. I used ODBC. I tried this but was forced to store big files in 1 MB chunks in bytea fields and create file from downloaded blocks So I'm looking a more efficient protocols to download backup file. Or should I really write code which divides backup file to 1 MB chunks and stores them in bytea field ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement backup protocol
Richard, Use scp. Open port 22 and allow only connections from the backup machine with a specified user (e.g. pgbackup). Alternatively, you might try dumping in a text-format and using rsync to transfer changes. I really do'nt want to open separate port for backup only. Pelase, can you recomment a solution which uses port 5432 owned by Postgres ? pg_read_file() can read only text files and is restricted only to superusers. How to add a function pg_read_backup() to Postgres which creates and returns backup file with download speed ? This problably requires implementing some file download protocol. Just don't try and do it within PG - use the tools the system provides. How to force postmaster to run the tool which system provides when it receives backup request instead of postgres child process ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement backup protocol
I don't see where doing the backup directly to another computer increases your safety margin, it may even lower it due to the increased potential for network issues messing up the backup cycle. Do it locally then SCP the completed (and compressed) file to another computer, which is what I do. (In fact I send it to THREE different computers in two physical locations, including one 900 miles away.) On 11/28/06, Andrus [EMAIL PROTECTED] wrote: My environment is a bit different. For safety, I need to create backups to separate computer over over internet. -- Mike Nolan
Re: [GENERAL] How to implement backup protocol
On Tue, 2006-11-28 at 19:34 +0200, Andrus wrote: You could use an *un*trusted procedural language to create a function to binary-read the backup from disk and return it as a bytea field. Not sure how efficient that is, though. You could then simply do select get_backup(); If you allow for parameters you could make it return certain backups based on, perhaps, timestamp of creation. Karsten, This id good idea but it forces to use Postgres protocol for downloading. This protocol has some timeouts which are too small for large file download. Postgres protocol has also a lot of overhead added to downloadable data. It also requires that whole downloadable file must fit into memory. You could backup to a postgresql large object, and then transmit that. Would that be more efficient? It would be kind of strange, but it might work if you can't open any other ports. Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to implement backup protocol
On Tue, Nov 28, 2006 at 07:23:44PM +0200, Andrus wrote: Pelase, can you recomment a solution which uses port 5432 owned by Postgres If you think you know your usage pattern: Have cron stop PostgreSQL at, say, 2am. Have cron start ssh on port 5432 at 2:05am if PG is down. Have cron shutdown ssh on port 5432 at 2:55am. Have cron KILL ssh on port 5432 if need be. Have cron start PostgreSQL at 3am if ssh is down. How to force postmaster to run the tool which system provides when it receives backup request instead of postgres child process ? There is no (builtin/obvious/easy) way for good reason. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement backup protocol
On Tue, Nov 28, 2006 at 07:34:56PM +0200, Andrus wrote: This id good idea but it forces to use Postgres protocol for downloading. Why, of course. This protocol has some timeouts which are too small for large file download. For sane values of large I doubt this is true. A field in PG can store about 1 GB of data (says the FAQ) and the protocol better be able to hand out as much. It may be that you need to increase statement_timeout - which can be done on a per-session basis. Postgres protocol has also a lot of overhead added to downloadable data. Yes. But you wanted to use port 5432 on a machine already running PG. Not sure but using a binary cursor might improve things. Using a client library capable of the v3 (?) protocol should significantly lower the overhead, too. It also requires that whole downloadable file must fit into memory. My PG knowledge isn't up to this task but I have a sneaking suspicion this isn't really enforced by PG itself. ODBC I tried this but was forced to store big files in 1 MB chunks in bytea fields and create file from downloaded blocks Other client libraries may do better here. Or should I really write code which divides backup file to 1 MB chunks and stores them in bytea field ? No. I would not even store them in the database at all. I would use the untrusted language function to read the file from disk and return a (virtual) bytea field (which doesn't exist in the database). Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to implement backup protocol
Andrus wrote: Richard, Use scp. Open port 22 and allow only connections from the backup machine with a specified user (e.g. pgbackup). Alternatively, you might try dumping in a text-format and using rsync to transfer changes. I really do'nt want to open separate port for backup only. Pelase, can you recomment a solution which uses port 5432 owned by Postgres ? No. You seem dead set on making your life harder than it needs to be. If you really don't want to have the ssh port open then set the backup to run from a cron-job on the main server and have it scp the result over to the backup server. That way only the backup server needs ssh open. pg_read_file() can read only text files and is restricted only to superusers. How to add a function pg_read_backup() to Postgres which creates and returns backup file with download speed ? This problably requires implementing some file download protocol. Just don't try and do it within PG - use the tools the system provides. How to force postmaster to run the tool which system provides when it receives backup request instead of postgres child process ? Don't. You're just making life difficult for yourself. Use the standard Unix solution for such things - ssh. How are you installing updates without ssh access to the server? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to implement backup protocol
Karsten Hilbert [EMAIL PROTECTED] writes: Not sure but using a binary cursor might improve things. Why not use COPY protocol? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to implement backup protocol
On þri, 2006-11-28 at 19:23 +0200, Andrus wrote: Richard, I really do'nt want to open separate port for backup only. Pelase, can you recomment a solution which uses port 5432 owned by Postgres I do not want to advice you to do things that might be counter your company's security policies, but you could set up a portforwarder on your database machine to pass incoming port 5432 requests from the backup machine to sshd, but let all other source ips go to postgres alternatively, if you have control of cron, and if the firewall restrictons are for incoming only, and if you have open ssh port on some other machine, such as the backupserver, you can have cron do the compressed backup, and send it via scp to the backupserver. How to force postmaster to run the tool which system provides when it receives backup request instead of postgres child process ? if you cannot control cron, but the firewall restrictions are only for incoming requests, you might try using some untrusted procedural language to start a backup script locally, that finishes by copying the compressed backup to the backupserver. whatever you choose to do, you should still get permission to do it. security admins are not amused when users use tricks to get around restrictons. maybe they would consider opening the ssh port if you make it clear that they may restrict it to requests from the backup machine? gnari ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
