lock entire database

#1

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.
------------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#2

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:

Quote:

In PGSQL, is it possible to lock the entire database??!!

Not that I know of, but why would you want to anyway?

Quote:

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 (AT) postgresql (DOT) org

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

#3

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:

Quote:

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 (AT) postgresql (DOT) org

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#4

Steve Tucknott wrote:

Quote:

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

#5

Benjamin wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#6

On Fri, 2004-08-06 at 12:19, Ron St-Pierre wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#7

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:

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:

Quote:

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 (AT) postgresql (DOT) org

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

#8

Scott Marlowe wrote:

Quote:

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.
------------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#9

On Sun, 2004-08-08 at 23:35, Benjamin wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#10

On Sun, 2004-08-08 at 23:35, Benjamin wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)