Thread: lock entire database
In PGSQL, is it possible to lock the entire database??!! This could be done with locking with individual table locking, but any way to lock the entire db?? If this is the only way, how do i get the listing of the tables? thru '\d' ?? and then 'cut' or 'sed ' on it to get the individual table names? -- Benjamin Jacob. Disclaimer : ------------------------------------------------------------------------------ If you are not the intended recipient of this transmission to whom it is addressed, or have received this transmission in error, you are hereby notified that any dissemination, distribution or copying of this transmission is strictly prohibited. Please notify us immediately and delete this e-mail from your system. The sender does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission, which cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, arrive at wrong address or contain viruses. If verification is required please request a hard-copy version. This e-mail contains only the personal opinions of the sender and does not represent an official communication from NetYantra of any manner. ------------------------------------------------------------------------------
Benjamin wrote: > > In PGSQL, is it possible to lock the entire database??!! Not that I know of, but why would you want to anyway? > > This could be done with locking with individual table locking, but any > way to lock the entire db?? > If this is the only way, how do i get the listing of the tables? thru > '\d' ?? and then 'cut' or 'sed ' on it to get the individual table names? Check out the docs at http://www.postgresql.org/docs/7.4/static/app-psql.html to see the psql commands. With the \d you can see just the tables, views, etc (eg \dt). Ron
We have a similar request.
We have a 'program' that does database 'structure' updates and do not want the users touching the database while this is going on. In Informix this was achieved by placing and EXCLUSIVE lock on the database itself.
Is there a (simple) way of 'locking out' specific users under PostGre to achieve the same end?
On Fri, 2004-08-06 at 16:32, Ron St-Pierre wrote:
We have a 'program' that does database 'structure' updates and do not want the users touching the database while this is going on. In Informix this was achieved by placing and EXCLUSIVE lock on the database itself.
Is there a (simple) way of 'locking out' specific users under PostGre to achieve the same end?
On Fri, 2004-08-06 at 16:32, Ron St-Pierre wrote:
Benjamin wrote: > > In PGSQL, is it possible to lock the entire database??!! Not that I know of, but why would you want to anyway? > > This could be done with locking with individual table locking, but any > way to lock the entire db?? > If this is the only way, how do i get the listing of the tables? thru > '\d' ?? and then 'cut' or 'sed ' on it to get the individual table names? Check out the docs at http://www.postgresql.org/docs/7.4/static/app-psql.html to see the psql commands. With the \d you can see just the tables, views, etc (eg \dt). Ron ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769 |
Generally the "solution" to locking the entire database is to keep a spare pg_hba.conf around that only allows a certain user to connect to do these things, and switching out from one pg_hba.conf to another as needed. Note, however, that DDL in PostgreSQL is fully transactable, so it's possible to do something like: begin; alter table xyz ... drop table abc... create table abc as... create index yada... commit; And none of the changes will show up for other users until the commit. Note that locking issues may lock users out of those tables being modified until the commit, but they definitely won't see the changes until commit. On Fri, 2004-08-06 at 10:28, Steve Tucknott wrote: > We have a similar request. > We have a 'program' that does database 'structure' updates and do not > want the users touching the database while this is going on. In > Informix this was achieved by placing and EXCLUSIVE lock on the > database itself. > Is there a (simple) way of 'locking out' specific users under PostGre > to achieve the same end? > On Fri, 2004-08-06 at 16:32, Ron St-Pierre wrote: > Benjamin wrote: > > > > > In PGSQL, is it possible to lock the entire database??!! > > Not that I know of, but why would you want to anyway? > > > > > This could be done with locking with individual table locking, but any > > way to lock the entire db?? > > If this is the only way, how do i get the listing of the tables? thru > > '\d' ?? and then 'cut' or 'sed ' on it to get the individual table names? > > Check out the docs at > http://www.postgresql.org/docs/7.4/static/app-psql.html to see the psql > commands. With the \d you can see just the tables, views, etc (eg \dt). > Ron > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > Regards, > > Steve Tucknott > > ReTSol Ltd > > DDI: 01903 828769
Benjamin wrote: > > Thanx Ron for that. > I got the listing of the tables. > The \d option gives u a formatter output. I just wanted a list , that > i cud loop on. > I guess that cud be done with a simple "SELECT tablename from > pg_tables where schemaname='public' ". > > Now to the need to lock the database. I need to backup the database at > run-time, on another machine, which is a backup for the first one. So > the data has to match exactly as on the first. So till the backup > faithfuly copies everything down, there shud be no changes on the main > machine. > Postgres uses MVCC (multiversion concurrency control) which basically means that each transaction takes a snapshot of the database when a user performs a query (read, update, whatever). So if a pg_dump of the entire database occurs within a single transaction, this would ensure that you have a valid snapshot at one particular instance. *Can anyone verify if this is indeed true*? If that is true then a pg_dump should solve your problem. Ron ps pls post your responses to the list too.
Steve Tucknott wrote: > We have a similar request. > We have a 'program' that does database 'structure' updates and do not > want the users touching the database while this is going on. In Informix > this was achieved by placing and EXCLUSIVE lock on the database itself. > Is there a (simple) way of 'locking out' specific users under PostGre to > achieve the same end? Database structures change can be inserted inside a transaction, so you don't need to lock the entire DB: begin; <change your schema>; commit; Regards Gaetano Mendola
On Fri, 2004-08-06 at 12:19, Ron St-Pierre wrote: > Benjamin wrote: > > > > > Thanx Ron for that. > > I got the listing of the tables. > > The \d option gives u a formatter output. I just wanted a list , that > > i cud loop on. > > I guess that cud be done with a simple "SELECT tablename from > > pg_tables where schemaname='public' ". > > > > Now to the need to lock the database. I need to backup the database at > > run-time, on another machine, which is a backup for the first one. So > > the data has to match exactly as on the first. So till the backup > > faithfuly copies everything down, there shud be no changes on the main > > machine. > > > Postgres uses MVCC (multiversion concurrency control) which basically > means that each transaction takes a snapshot of the database when a user > performs a query (read, update, whatever). So if a pg_dump of the entire > database occurs within a single transaction, this would ensure that you > have a valid snapshot at one particular instance. *Can anyone verify if > this is indeed true*? > > If that is true then a pg_dump should solve your problem. Yes it is true, but only for a single database in the cluster at a time. If you have data in two databases in the pgsql cluster, each will be started at a different point in time. However, it may be that the poster is trying to do something like Point in Time recovery (he could just test 8.0 and see how PITR works, it might be a better option). IFF he needs the two databases to be exactly the same, then something like a pooling connection thingie like pgpool will get turned on and starts writing the same updates to both databases at the same time, he might need to truly lock out all changes for a bit. OR maybe he's wanting to backup the one server and take it offline, so changes made would be lost there. I think we may need a bit more explanation on just what Benjamin is trying to accomplish to give the right answer.
We moved away from transaction based database structure changes due to possible effects of long transactions causing the updates to abort - the second conf file sounds the best bet. That way we can ensure that root only has access while the changes take place.
Thanks for the help - much appreciated
On Fri, 2004-08-06 at 18:23, Scott Marlowe wrote:
Thanks for the help - much appreciated
On Fri, 2004-08-06 at 18:23, Scott Marlowe wrote:
Generally the "solution" to locking the entire database is to keep a spare pg_hba.conf around that only allows a certain user to connect to do these things, and switching out from one pg_hba.conf to another as needed. Note, however, that DDL in PostgreSQL is fully transactable, so it's possible to do something like: begin; alter table xyz ... drop table abc... create table abc as... create index yada... commit; And none of the changes will show up for other users until the commit. Note that locking issues may lock users out of those tables being modified until the commit, but they definitely won't see the changes until commit. On Fri, 2004-08-06 at 10:28, Steve Tucknott wrote: > We have a similar request. > We have a 'program' that does database 'structure' updates and do not > want the users touching the database while this is going on. In > Informix this was achieved by placing and EXCLUSIVE lock on the > database itself. > Is there a (simple) way of 'locking out' specific users under PostGre > to achieve the same end? > On Fri, 2004-08-06 at 16:32, Ron St-Pierre wrote: > Benjamin wrote: > > > > > In PGSQL, is it possible to lock the entire database??!! > > Not that I know of, but why would you want to anyway? > > > > > This could be done with locking with individual table locking, but any > > way to lock the entire db?? > > If this is the only way, how do i get the listing of the tables? thru > > '\d' ?? and then 'cut' or 'sed ' on it to get the individual table names? > > Check out the docs at > http://www.postgresql.org/docs/7.4/static/app-psql.html to see the psql > commands. With the \d you can see just the tables, views, etc (eg \dt). > Ron > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > Regards, > > Steve Tucknott > > ReTSol Ltd > > DDI: 01903 828769
Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769 |
On Sun, 2004-08-08 at 23:35, Benjamin wrote: > Scott Marlowe wrote: > > >On Fri, 2004-08-06 at 12:19, Ron St-Pierre wrote: > > > >>Benjamin wrote: > >> > >>>Thanx Ron for that. > >>>I got the listing of the tables. > >>>The \d option gives u a formatter output. I just wanted a list , that > >>>i cud loop on. > >>>I guess that cud be done with a simple "SELECT tablename from > >>>pg_tables where schemaname='public' ". > >>> > >>>Now to the need to lock the database. I need to backup the database at > >>>run-time, on another machine, which is a backup for the first one. So > >>>the data has to match exactly as on the first. So till the backup > >>>faithfuly copies everything down, there shud be no changes on the main > >>>machine. > >>> > >>Postgres uses MVCC (multiversion concurrency control) which basically > >>means that each transaction takes a snapshot of the database when a user > >>performs a query (read, update, whatever). So if a pg_dump of the entire > >>database occurs within a single transaction, this would ensure that you > >>have a valid snapshot at one particular instance. *Can anyone verify if > >>this is indeed true*? > >> > >>If that is true then a pg_dump should solve your problem. > >> > > > >Yes it is true, but only for a single database in the cluster at a > >time. If you have data in two databases in the pgsql cluster, each will > >be started at a different point in time. > > > >However, it may be that the poster is trying to do something like Point > >in Time recovery (he could just test 8.0 and see how PITR works, it > >might be a better option). IFF he needs the two databases to be exactly > >the same, then something like a pooling connection thingie like pgpool > >will get turned on and starts writing the same updates to both databases > >at the same time, he might need to truly lock out all changes for a > >bit. OR maybe he's wanting to backup the one server and take it > >offline, so changes made would be lost there. > > > >I think we may need a bit more explanation on just what Benjamin is > >trying to accomplish to give the right answer. > > > Ok. The scene now. > Machine A is the Primary, and Machine B is the backup for A. > When B is booting up, it has to duplicate the entire pgsql db from A. > As Ron said, cud do with a pg_dump. But, i guess, pg_dump takes quite > some time. As A is already up, it wud be unwise to lock the db for so > long. Also, even if i do go ahead with pg_dump, and then do a pg_restore > on B, by the time data is being pg_restore'ed on B, a query cud modify/ > update the db on A. > My idea was to lock the db on A, scp the required files onto B and then > unlock db on A. > > Is the picture clear now? > Yes, you should use slony or some other replication method if possible.
On Sun, 2004-08-08 at 23:35, Benjamin wrote: > Scott Marlowe wrote: > Ok. The scene now. > Machine A is the Primary, and Machine B is the backup for A. > When B is booting up, it has to duplicate the entire pgsql db from A. > As Ron said, cud do with a pg_dump. But, i guess, pg_dump takes quite > some time. As A is already up, it wud be unwise to lock the db for so > long. Also, even if i do go ahead with pg_dump, and then do a pg_restore > on B, by the time data is being pg_restore'ed on B, a query cud modify/ > update the db on A. > My idea was to lock the db on A, scp the required files onto B and then > unlock db on A. > > Is the picture clear now? > In addition to my previous post, or wait until 8.0 and use pitr to do this.
Scott Marlowe wrote: >On Fri, 2004-08-06 at 12:19, Ron St-Pierre wrote: > >>Benjamin wrote: >> >>>Thanx Ron for that. >>>I got the listing of the tables. >>>The \d option gives u a formatter output. I just wanted a list , that >>>i cud loop on. >>>I guess that cud be done with a simple "SELECT tablename from >>>pg_tables where schemaname='public' ". >>> >>>Now to the need to lock the database. I need to backup the database at >>>run-time, on another machine, which is a backup for the first one. So >>>the data has to match exactly as on the first. So till the backup >>>faithfuly copies everything down, there shud be no changes on the main >>>machine. >>> >>Postgres uses MVCC (multiversion concurrency control) which basically >>means that each transaction takes a snapshot of the database when a user >>performs a query (read, update, whatever). So if a pg_dump of the entire >>database occurs within a single transaction, this would ensure that you >>have a valid snapshot at one particular instance. *Can anyone verify if >>this is indeed true*? >> >>If that is true then a pg_dump should solve your problem. >> > >Yes it is true, but only for a single database in the cluster at a >time. If you have data in two databases in the pgsql cluster, each will >be started at a different point in time. > >However, it may be that the poster is trying to do something like Point >in Time recovery (he could just test 8.0 and see how PITR works, it >might be a better option). IFF he needs the two databases to be exactly >the same, then something like a pooling connection thingie like pgpool >will get turned on and starts writing the same updates to both databases >at the same time, he might need to truly lock out all changes for a >bit. OR maybe he's wanting to backup the one server and take it >offline, so changes made would be lost there. > >I think we may need a bit more explanation on just what Benjamin is >trying to accomplish to give the right answer. > Ok. The scene now. Machine A is the Primary, and Machine B is the backup for A. When B is booting up, it has to duplicate the entire pgsql db from A. As Ron said, cud do with a pg_dump. But, i guess, pg_dump takes quite some time. As A is already up, it wud be unwise to lock the db for so long. Also, even if i do go ahead with pg_dump, and then do a pg_restore on B, by the time data is being pg_restore'ed on B, a query cud modify/ update the db on A. My idea was to lock the db on A, scp the required files onto B and then unlock db on A. Is the picture clear now? -- Benjamin Jacob. Disclaimer : ------------------------------------------------------------------------------ If you are not the intended recipient of this transmission to whom it is addressed, or have received this transmission in error, you are hereby notified that any dissemination, distribution or copying of this transmission is strictly prohibited. Please notify us immediately and delete this e-mail from your system. The sender does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission, which cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, arrive at wrong address or contain viruses. If verification is required please request a hard-copy version. This e-mail contains only the personal opinions of the sender and does not represent an official communication from NetYantra of any manner. ------------------------------------------------------------------------------
Benjamin <benjamin@netyantra.com> writes: > My idea was to lock the db on A, scp the required files onto B and then > unlock db on A. The only adequate "lock" for that sort of thing is to shut down the postmaster on A. Anything less is simply not trustworthy. There is support coming up in 8.0 for WAL archiving and point-in-time recovery. With that, you could do the scp without any lock and then fix up discrepancies by replaying the WAL archives for the interval that the scp was running. Furthermore you could continue to ship WAL segments to B to keep it up to date with A, without needing repeated full scp's. See http://developer.postgresql.org/docs/postgres/backup.html for some preliminary documentation about this. Slony looks like a pretty good alternative too, and it's available now. But don't bother trying to roll your own replication setup. It's unlikely that you can easily build a reliable one. regards, tom lane
Benjamin wrote: > Machine A is the Primary, and Machine B is the backup for A. > When B is booting up, it has to duplicate the entire pgsql db from A. > As Ron said, cud do with a pg_dump. But, i guess, pg_dump takes quite > some time. As A is already up, it wud be unwise to lock the db for so > long. Also, even if i do go ahead with pg_dump, and then do a pg_restore > on B, by the time data is being pg_restore'ed on B, a query cud modify/ > update the db on A. > My idea was to lock the db on A, scp the required files onto B and then > unlock db on A. If I understand you correctly, "Slony" will do exactly what you want, without the need to lock any DB: http://www.slony.org Slony 1.0 has been released recently, which was mentioned prominently on PostgreSQL's homepage. It's a master-slave replication system. Hope that helps. Best regards Oliver -- Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München Any opinions expressed in this message may be personal to the author and may not necessarily reflect the opinions of secnetix in any way. "UNIX was not designed to stop you from doing stupid things, because that would also stop you from doing clever things." -- Doug Gwyn
Oliver Fromme wrote: >Benjamin wrote: > > Machine A is the Primary, and Machine B is the backup for A. > > When B is booting up, it has to duplicate the entire pgsql db from A. > > As Ron said, cud do with a pg_dump. But, i guess, pg_dump takes quite > > some time. As A is already up, it wud be unwise to lock the db for so > > long. Also, even if i do go ahead with pg_dump, and then do a pg_restore > > on B, by the time data is being pg_restore'ed on B, a query cud modify/ > > update the db on A. > > My idea was to lock the db on A, scp the required files onto B and then > > unlock db on A. > >If I understand you correctly, "Slony" will do exactly what you >want, without the need to lock any DB: http://www.slony.org >Slony 1.0 has been released recently, which was mentioned >prominently on PostgreSQL's homepage. It's a master-slave >replication system. > >Hope that helps. > >Best regards > Oliver > Thanx everyone. Yea.. slony seems to be good. We just might HAVE to use that in the near future. But as of now, we r shutting down pgsql on A, while the duplication is being done, as Tom suggested. -- Benjamin Jacob. Disclaimer : ------------------------------------------------------------------------------ If you are not the intended recipient of this transmission to whom it is addressed, or have received this transmission in error, you are hereby notified that any dissemination, distribution or copying of this transmission is strictly prohibited. Please notify us immediately and delete this e-mail from your system. The sender does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission, which cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, arrive at wrong address or contain viruses. If verification is required please request a hard-copy version. This e-mail contains only the personal opinions of the sender and does not represent an official communication from NetYantra of any manner. ------------------------------------------------------------------------------