From srichardjanet@charter.net Sat Jul 30 20:00:51 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5C15052A1E for ; Sat, 30 Jul 2005 17:00:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34559-07 for ; Sat, 30 Jul 2005 20:00:47 +0000 (GMT) Received: from mxsf28.cluster1.charter.net (mxsf28.cluster1.charter.net [209.225.28.228]) by svr1.postgresql.org (Postfix) with ESMTP id E79A5529BD for ; Sat, 30 Jul 2005 17:00:45 -0300 (ADT) Received: from mxip03a.cluster1.charter.net (mxip03a.cluster1.charter.net [209.225.28.133]) by mxsf28.cluster1.charter.net (8.12.11/8.12.11) with ESMTP id j6UK0jWm024748 for ; Sat, 30 Jul 2005 16:00:46 -0400 Received: from 68-186-58-148.dhcp.mghl.ca.charter.com (HELO irene) ([68.186.58.148]) by mxip03a.cluster1.charter.net with SMTP; 30 Jul 2005 16:00:46 -0400 X-IronPort-AV: i="3.95,155,1120449600"; d="scan'208,217"; a="1199758829:sNHT184467592" Message-ID: <01a201c59541$59648560$6401a8c0@irene> From: "N/A" To: Subject: Question on placing database on a network attached storage device Date: Sat, 30 Jul 2005 13:00:32 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_019F_01C59506.AB745F00" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 x-mimeole: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.433 tagged_above=0 required=5 tests=DNS_FROM_RFC_POST, HTML_30_40, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200507/258 X-Sequence-Number: 18314 This is a multi-part message in MIME format. ------=_NextPart_000_019F_01C59506.AB745F00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I'm putting together a mission critical application which will use a = Postgresql database, and I am trying to decide if I should place my = Postgresql database on a network attached storage appliance or on local = disks. =20 If I place the database on the NAS, it will get "backed up" by the NAS = device's "snap" type of images. That means the NAS will make a copy of = the delta, or difference, between the current version of the files on = it, and the version from the last time the "snap" was done. The "snap" = images will be dumped to tape at some interval of time. I'm concerned about using a "snap" of an open and active database to = restore the database if the need arises. Now, postgresql is pretty good = about keeping checkpoints and recovering, but I'm still concerned about = data integrity, (who knows what state any internal pointers are in when = the "snap is taken), and as my application will need to archive groups = of files that have to be kept together in order for the data in those = files to be meaningful, (a group of files could be in the process of = being archived to the database, the NAS takes a "snap" when only some of = the files have been written in the database, and then the rest of the = files are written in the database). There is also the question of = speed, Network attached storage vs. local disks, (network speed is = pretty good 100 Meg and will be going to 1 Gig in the future). I suppose I could place the database on the NAS, shutdown the database = when a backup of the database is desired, have Postgresql create the = database re-creation script, let the NAS take it's "snap", and then = bring the database back up. Any suggestions, experiences, or observations? ------=_NextPart_000_019F_01C59506.AB745F00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I'm putting together a mission critical = application=20 which will use a Postgresql database, and I am trying to decide if = I should=20 place my Postgresql database on a network attached storage appliance or = on local=20 disks. 
 
If I place the database on the NAS, it = will get=20 "backed up" by the NAS device's "snap" type of images.  That means = the NAS=20 will make a copy of the delta, or difference, between the current = version of the=20 files on it, and the version from the last time the "snap" was = done.  The=20 "snap" images will be dumped to tape at some interval of = time.
 
I'm concerned about using a "snap" of = an open and=20 active database to restore the database if the need arises.  Now,=20 postgresql is pretty good about keeping checkpoints and recovering, but = I'm=20 still concerned about data integrity, (who knows what state any internal = pointers are in when the "snap is taken), and as my application will = need to=20 archive groups of files that have to be kept together in order for the = data in=20 those files to be meaningful, (a group of files could be in the process = of being=20 archived to the database, the NAS takes a "snap" when only some of the = files=20 have been written in the database, and then the rest of the files are = written in=20 the database).  There is also the question of speed, Network = attached=20 storage vs. local disks, (network speed is pretty good 100 Meg and will = be going=20 to 1 Gig in the future).
 
I suppose I could place the database on = the NAS,=20 shutdown the database when a backup of the database is desired, have=20 Postgresql create the database re-creation script, let the NAS take = it's=20 "snap", and then bring the database back up.
 
Any suggestions, experiences, or=20 observations?
------=_NextPart_000_019F_01C59506.AB745F00-- From chris@metatrontech.com Sat Jul 30 20:44:11 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EA36452AC7 for ; Sat, 30 Jul 2005 17:44:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43880-02 for ; Sat, 30 Jul 2005 20:44:05 +0000 (GMT) Received: from mail.travelamericas.com (unknown [206.130.134.147]) by svr1.postgresql.org (Postfix) with SMTP id 8E43C5292D for ; Sat, 30 Jul 2005 17:44:00 -0300 (ADT) Received: (qmail 3390 invoked from network); 30 Jul 2005 20:44:01 -0000 Received: from unknown (HELO ?10.0.0.253?) (10.0.0.253) by verkiel.travelamericas.com with SMTP; 30 Jul 2005 20:44:01 -0000 Message-ID: <42EBE690.2040100@metatrontech.com> Date: Sat, 30 Jul 2005 13:44:00 -0700 From: Chris Travers User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: N/A Cc: pgsql-admin@postgresql.org Subject: Re: Question on placing database on a network attached storage References: <01a201c59541$59648560$6401a8c0@irene> In-Reply-To: <01a201c59541$59648560$6401a8c0@irene> Content-Type: multipart/mixed; boundary="------------090100090707010005050201" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.062 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200508/50 X-Sequence-Number: 18369 This is a multi-part message in MIME format. --------------090100090707010005050201 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit N/A wrote: > Hi, > > I'm putting together a mission critical application which will use a > Postgresql database, and I am trying to decide if I should place my > Postgresql database on a network attached storage appliance or on > local disks. Wherever possible, I think that local storage is almost always best as it is a less complex system (hence less can go wrong). However, you will probably get better answers if you tell us what sort of NAS you are looking at using, how it will connect to your system, etc. > > If I place the database on the NAS, it will get "backed up" by the NAS > device's "snap" type of images. That means the NAS will make a copy > of the delta, or difference, between the current version of the files > on it, and the version from the last time the "snap" was done. The > "snap" images will be dumped to tape at some interval of time. > How internally consistant is this snap? I.e. if the file is changed while the snap is being taken, what happens? This behavior may be product-dependnant and has serious ramifications for your data. > I'm concerned about using a "snap" of an open and active database to > restore the database if the need arises. Now, postgresql is pretty > good about keeping checkpoints and recovering, but I'm still concerned > about data integrity, (who knows what state any internal pointers are > in when the "snap is taken), and as my application will need to > archive groups of files that have to be kept together in order for the > data in those files to be meaningful, (a group of files could be in > the process of being archived to the database, the NAS takes a "snap" > when only some of the files have been written in the database, and > then the rest of the files are written in the database). There is > also the question of speed, Network attached storage vs. local disks, > (network speed is pretty good 100 Meg and will be going to 1 Gig in > the future). Personally I think that there are better approaches than NAS for this, but again I am unwilling to say "never use it." > > I suppose I could place the database on the NAS, shutdown the database > when a backup of the database is desired, have Postgresql create the > database re-creation script, let the NAS take it's "snap", and then > bring the database back up. > > Any suggestions, experiences, or observations? Best Wishes, Chris Travers Metatron Technology Consulting --------------090100090707010005050201 Content-Type: text/x-vcard; charset=utf-8; name="chris.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="chris.vcf" begin:vcard fn:Chris Travers n:Travers;Chris email;internet:chris@metatrontech.com x-mozilla-html:FALSE version:2.1 end:vcard --------------090100090707010005050201-- From srichardjanet@charter.net Sat Jul 30 21:28:01 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A6B1A52B82 for ; Sat, 30 Jul 2005 18:28:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49659-08 for ; Sat, 30 Jul 2005 21:27:55 +0000 (GMT) Received: from mxsf37.cluster1.charter.net (mxsf37.cluster1.charter.net [209.225.28.162]) by svr1.postgresql.org (Postfix) with ESMTP id 912D652B07 for ; Sat, 30 Jul 2005 18:27:54 -0300 (ADT) Received: from mxip28a.cluster1.charter.net (mxip28a.cluster1.charter.net [209.225.28.187]) by mxsf37.cluster1.charter.net (8.12.11/8.12.11) with ESMTP id j6ULRtXa002846 for ; Sat, 30 Jul 2005 17:27:55 -0400 Received: from 68-186-58-148.dhcp.mghl.ca.charter.com (HELO irene) ([68.186.58.148]) by mxip28a.cluster1.charter.net with SMTP; 30 Jul 2005 17:27:55 -0400 X-IronPort-AV: i="3.95,155,1120449600"; d="scan'208"; a="1962797:sNHT20562432" Message-ID: <01c901c5954d$869ec660$6401a8c0@irene> From: "N/A" To: "Chris Travers" Cc: References: <01a201c59541$59648560$6401a8c0@irene> <42EBE690.2040100@metatrontech.com> Subject: Re: Question on placing database on a network attached storage device Date: Sat, 30 Jul 2005 14:27:44 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 x-mimeole: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.06 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST X-Spam-Level: * X-Archive-Number: 200507/259 X-Sequence-Number: 18315 Chris, The NAS is a Network Appliance 920c. I understand that an individual file while be "intact" during a snap, (not sure I believe it, but that's what I'm told). The NAS may be able to monitor the state of an individual file and not "snap" it if it is on an "open" state or has a pending write, or some such, but as the NAS has no knowledge of groups of files that need to exist in a known state, (it deals on the file level), it cannot guarantee a consistent state across a group of files. (The database will be on the NAS and will be NFS mounted filesystem to a Linux Redhat 3 running the application.) ----- Original Message ----- From: "Chris Travers" To: "N/A" Cc: Sent: Saturday, July 30, 2005 1:44 PM Subject: Re: [ADMIN] Question on placing database on a network attached storage device > N/A wrote: > > > Hi, > > > > I'm putting together a mission critical application which will use a > > Postgresql database, and I am trying to decide if I should place my > > Postgresql database on a network attached storage appliance or on > > local disks. > > Wherever possible, I think that local storage is almost always best as > it is a less complex system (hence less can go wrong). However, you > will probably get better answers if you tell us what sort of NAS you are > looking at using, how it will connect to your system, etc. > > > > > If I place the database on the NAS, it will get "backed up" by the NAS > > device's "snap" type of images. That means the NAS will make a copy > > of the delta, or difference, between the current version of the files > > on it, and the version from the last time the "snap" was done. The > > "snap" images will be dumped to tape at some interval of time. > > > > How internally consistant is this snap? I.e. if the file is changed > while the snap is being taken, what happens? This behavior may be > product-dependnant and has serious ramifications for your data. > > > I'm concerned about using a "snap" of an open and active database to > > restore the database if the need arises. Now, postgresql is pretty > > good about keeping checkpoints and recovering, but I'm still concerned > > about data integrity, (who knows what state any internal pointers are > > in when the "snap is taken), and as my application will need to > > archive groups of files that have to be kept together in order for the > > data in those files to be meaningful, (a group of files could be in > > the process of being archived to the database, the NAS takes a "snap" > > when only some of the files have been written in the database, and > > then the rest of the files are written in the database). There is > > also the question of speed, Network attached storage vs. local disks, > > (network speed is pretty good 100 Meg and will be going to 1 Gig in > > the future). > > Personally I think that there are better approaches than NAS for this, > but again I am unwilling to say "never use it." > > > > > I suppose I could place the database on the NAS, shutdown the database > > when a backup of the database is desired, have Postgresql create the > > database re-creation script, let the NAS take it's "snap", and then > > bring the database back up. > > > > Any suggestions, experiences, or observations? > > Best Wishes, > Chris Travers > Metatron Technology Consulting > From chris@travelamericas.com Sat Jul 30 21:59:46 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 851085280C for ; Sat, 30 Jul 2005 18:59:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64919-02 for ; Sat, 30 Jul 2005 21:59:41 +0000 (GMT) Received: from mail.travelamericas.com (unknown [206.130.134.147]) by svr1.postgresql.org (Postfix) with SMTP id A510152AB4 for ; Sat, 30 Jul 2005 18:59:29 -0300 (ADT) Received: (qmail 4203 invoked from network); 30 Jul 2005 21:59:32 -0000 Received: from unknown (HELO ?10.0.0.253?) (10.0.0.253) by verkiel.travelamericas.com with SMTP; 30 Jul 2005 21:59:32 -0000 Message-ID: <42EBF842.5090306@travelamericas.com> Date: Sat, 30 Jul 2005 14:59:30 -0700 From: Chris Travers User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: N/A Cc: Chris Travers , pgsql-admin@postgresql.org Subject: Re: Question on placing database on a network attached storage References: <01a201c59541$59648560$6401a8c0@irene> <42EBE690.2040100@metatrontech.com> <01c901c5954d$869ec660$6401a8c0@irene> In-Reply-To: <01c901c5954d$869ec660$6401a8c0@irene> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.037 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200507/260 X-Sequence-Number: 18316 N/A wrote: >Chris, > >The NAS is a Network Appliance 920c. I understand that an individual file >while be "intact" during a snap, (not sure I believe it, but that's what I'm >told). > Depending on the way that data is journalled, this may be a possibility (istr XFS is able to do this). > The NAS may be able to monitor the state of an individual file and >not "snap" it if it is on an "open" state or has a pending write, or some >such, but as the NAS has no knowledge of groups of files that need to exist >in a known state, (it deals on the file level), it cannot guarantee a >consistent state across a group of files. > That is a serious concern. Every table in PostgreSQL consists of at least one physical file. Transactions may touch several of these files. While it is not impossible to solve this problem (as I mentioned, I think XFS has this capability), it is hardly trivial or something you would normally expect. Also I wonder what the impact of several Postmasters trying to access data over NFS would be. Best Wishes, Chris Travers From jeff@frostconsultingllc.com Sat Jul 30 22:56:13 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E92CD5297B for ; Sat, 30 Jul 2005 19:56:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69056-08 for ; Sat, 30 Jul 2005 22:56:07 +0000 (GMT) Received: from smtp113.sbc.mail.mud.yahoo.com (smtp113.sbc.mail.mud.yahoo.com [68.142.198.212]) by svr1.postgresql.org (Postfix) with SMTP id 563635287D for ; Sat, 30 Jul 2005 19:56:06 -0300 (ADT) Received: (qmail 26903 invoked from network); 30 Jul 2005 22:56:09 -0000 Received: from unknown (HELO discord.dyndns.org) (jeffroe996@sbcglobal.net@69.227.49.166 with plain) by smtp113.sbc.mail.mud.yahoo.com with SMTP; 30 Jul 2005 22:56:08 -0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by discord.dyndns.org (8.13.1/8.13.1) with ESMTP id j6UMu5Ej004118 for ; Sat, 30 Jul 2005 15:56:05 -0700 Date: Sat, 30 Jul 2005 15:56:05 -0700 (PDT) From: Jeff Frost X-X-Sender: jeff@discord.dyndns.org To: pgsql-admin@postgresql.org Subject: Re: Question on placing database on a network attached In-Reply-To: <01c901c5954d$869ec660$6401a8c0@irene> Message-ID: References: <01a201c59541$59648560$6401a8c0@irene> <42EBE690.2040100@metatrontech.com> <01c901c5954d$869ec660$6401a8c0@irene> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.037 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200507/261 X-Sequence-Number: 18317 If you are using postgresql-8.0.x, then you can use PITR and base backups to achieve a fairly consistent backup on a NAS which restores rather quickly. If the NAS supports hard links, then you can use the --link-dest option of rsync to make the base backups quite fast and still retain more than one base backup at a time. The only problem with this solution is that you need at least as much disk space as your PGDATA directory contains, plus enough space for the WAL archives between base backups. If the NAS does not support hard links, but does do snapshotting as you indicate, then using rsync with the --partial flag against the previous base backup would also be quite fast and bandwidth efficient. The snapshotting on the NAS would take care of the prior version base backup for you and all you need do is rotate out the WAL archives according to how many snapshots you care about. Using PITR in this way has the added benefit of allowing you to restore the DB to some point in the past if need be. -- Jeff Frost, Owner Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 On Sat, 30 Jul 2005, N/A wrote: > The NAS is a Network Appliance 920c. I understand that an individual file > while be "intact" during a snap, (not sure I believe it, but that's what I'm > told). The NAS may be able to monitor the state of an individual file and > not "snap" it if it is on an "open" state or has a pending write, or some > such, but as the NAS has no knowledge of groups of files that need to exist > in a known state, (it deals on the file level), it cannot guarantee a > consistent state across a group of files. (The database will be on the NAS > and will be NFS mounted filesystem to a Linux Redhat 3 running the > application.) From alvherre@alvh.no-ip.org Sun Jul 31 02:29:24 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 909A552A02 for ; Sat, 30 Jul 2005 23:29:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 10352-10 for ; Sun, 31 Jul 2005 02:29:20 +0000 (GMT) Received: from mr1.surnet.cl (smtp1.surnet.cl [216.155.73.162]) by svr1.postgresql.org (Postfix) with ESMTP id B540B52996 for ; Sat, 30 Jul 2005 23:29:13 -0300 (ADT) Received: from smtp1.surnet.cl (216.155.73.168) by mr1.surnet.cl (7.0.031.3) id 42587EDE0193EC12; Sat, 30 Jul 2005 22:28:37 -0400 Received: from smtp2.surnet.cl (smtp2.surnet.cl []) by mr1.surnet.cl ([216.155.73.168]); Sun, 31 Jul 2005 02:28:37 +0000 Received: from cluster.surnet.cl (216.155.73.164) by smtp2.surnet.cl (7.0.031.3) id 4259ADE300F8BA2C; Sat, 30 Jul 2005 22:28:45 -0400 Received: from alvh.no-ip.org (200.85.218.66) by cluster.surnet.cl (7.0.043) (authenticated as alvherre@surnet.cl) id 42B3EF6000605A3B; Sat, 30 Jul 2005 22:29:15 -0400 Received: by alvh.no-ip.org (Postfix, from userid 1000) id C457AC2D450; Sat, 30 Jul 2005 22:30:23 -0400 (CLT) Date: Sat, 30 Jul 2005 22:30:23 -0400 From: Alvaro Herrera To: Chris Travers Cc: N/A , Chris Travers , pgsql-admin@postgresql.org Subject: Re: Question on placing database on a network attached storage Message-ID: <20050731023023.GA26037@alvh.no-ip.org> Mail-Followup-To: Chris Travers , N/A , Chris Travers , pgsql-admin@postgresql.org References: <01a201c59541$59648560$6401a8c0@irene> <42EBE690.2040100@metatrontech.com> <01c901c5954d$869ec660$6401a8c0@irene> <42EBF842.5090306@travelamericas.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <42EBF842.5090306@travelamericas.com> User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.428 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO, RCVD_IN_NJABL_PROXY X-Spam-Level: * X-Archive-Number: 200507/262 X-Sequence-Number: 18318 On Sat, Jul 30, 2005 at 02:59:30PM -0700, Chris Travers wrote: > Also I wonder what the impact of several Postmasters trying to access > data over NFS would be. That will lead to corruption very quickly. -- Alvaro Herrera () "Estoy de acuerdo contigo en que la verdad absoluta no existe... El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama) From htomeh@firstam.com Fri Aug 5 13:28:13 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7395D52BF5 for ; Fri, 5 Aug 2005 10:28:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09120-09 for ; Fri, 5 Aug 2005 13:28:10 +0000 (GMT) Received: from wsmail05.firstam.com (unknown [208.246.100.41]) by svr1.postgresql.org (Postfix) with ESMTP id 1E77552BE7 for ; Fri, 5 Aug 2005 10:28:07 -0300 (ADT) Received: from 172.21.131.6 by wsmailap02.firstam.com with ESMTP ( Tumbleweed MMS SMTP Relay (MMS v5.6.3)); Fri, 05 Aug 2005 06:28:04 -0700 X-Server-Uuid: 7F57BFB8-2F8F-44C6-8C75-DD639B93CEA6 Received: from anammx01.ana.firstamdata.com ([192.168.173.35]) by famarp02.firstam.com (MOS 3.5.6-GR) with SMTP id JKI02787; Fri, 5 Aug 2005 06:27:16 -0700 (PDT) Received: from pisgana01sxch21.ana.firstamdata.com (Not Verified[192.168.173.71]) by mailgateway.firstam.com (post.office MTA v5.0 0924 ) with ESMTP id ; Fri, 05 Aug 2005 06:27:13 -0700 Received: from pisgana01sxch01.ana.firstamdata.com ([192.168.173.70]) by pisgana01sxch21.ana.firstamdata.com with Microsoft SMTPSVC(6.0.3790.211 ); Fri, 5 Aug 2005 06:27:12 -0700 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: Re: Question on placing database on a network attached Date: Fri, 5 Aug 2005 06:27:12 -0700 Message-ID: Thread-Topic: [ADMIN] Question on placing database on a network attached storage Thread-Index: AcWZet+go934eEK6RNCpYIuAirvOCAARTHOQ From: "Tomeh, Husam" To: "Chris Travers" , "N/A" Cc: pgsql-admin@postgresql.org X-OriginalArrivalTime: 05 Aug 2005 13:27:12.0792 (UTC) FILETIME=[638E1180:01C599C1] X-WSS-ID: 6EEDB6DD28C1222464-07-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.021 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/61 X-Sequence-Number: 18380 We're using similar technique but using SAN instead of NAS. The current approach is shutting down the source Postgres database, clone or snap it to the target server and then start the database back up. On Oracle, I never had to shut down the source, instead I issue alter tablespace begin backup, snap, and alter tablespace end backup to ensure a consistent image. These being/end backup commands, were originally there to place the database in state that you can perform a hot backup while the database is up and running. I think PostgreSQL has similar commands for hot backup - it will be interesting to know if anyone used them to perform a snap or clone while the source is up and running and not compromising database consistency. --=20 =20Husam =20 -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris Travers Sent: Saturday, July 30, 2005 1:44 PM To: N/A Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Question on placing database on a network attached storage N/A wrote: > Hi, > =20 > I'm putting together a mission critical application which will use a=20 > Postgresql database, and I am trying to decide if I should place my=20 > Postgresql database on a network attached storage appliance or on=20 > local disks. Wherever possible, I think that local storage is almost always best as=20 it is a less complex system (hence less can go wrong). However, you=20 will probably get better answers if you tell us what sort of NAS you are looking at using, how it will connect to your system, etc. > =20 > If I place the database on the NAS, it will get "backed up" by the NAS > device's "snap" type of images. That means the NAS will make a copy=20 > of the delta, or difference, between the current version of the files=20 > on it, and the version from the last time the "snap" was done. The=20 > "snap" images will be dumped to tape at some interval of time. > =20 How internally consistant is this snap? I.e. if the file is changed=20 while the snap is being taken, what happens? This behavior may be=20 product-dependnant and has serious ramifications for your data. > I'm concerned about using a "snap" of an open and active database to=20 > restore the database if the need arises. Now, postgresql is pretty=20 > good about keeping checkpoints and recovering, but I'm still concerned > about data integrity, (who knows what state any internal pointers are=20 > in when the "snap is taken), and as my application will need to=20 > archive groups of files that have to be kept together in order for the > data in those files to be meaningful, (a group of files could be in=20 > the process of being archived to the database, the NAS takes a "snap"=20 > when only some of the files have been written in the database, and=20 > then the rest of the files are written in the database). There is=20 > also the question of speed, Network attached storage vs. local disks,=20 > (network speed is pretty good 100 Meg and will be going to 1 Gig in=20 > the future). Personally I think that there are better approaches than NAS for this,=20 but again I am unwilling to say "never use it." > =20 > I suppose I could place the database on the NAS, shutdown the database > when a backup of the database is desired, have Postgresql create the=20 > database re-creation script, let the NAS take it's "snap", and then=20 > bring the database back up. > =20 > Any suggestions, experiences, or observations? Best Wishes, Chris Travers Metatron Technology Consulting ********************************************************************** This message contains confidential information intended only for the=20 use of the addressee(s) named above and may contain information that=20 is legally privileged. If you are not the addressee, or the person=20 responsible for delivering it to the addressee, you are hereby=20 notified that reading, disseminating, distributing or copying this=20 message is strictly prohibited. If you have received this message by=20 mistake, please immediately notify us by replying to the message and=20 delete the original message immediately thereafter. Thank you. FADLD Tag **********************************************************************