Thread: FW: Blocking access to the database??
If I had to reorganize the database, I would prefer to terminate the connections. Any way to do that without having to restart the db server? Could I kill the PID's for the users (Linux RedHat) In order to modify a user's permissions, must I restart the db server? Can one do an SQL-dump while users are modifying the data? -----Original Message----- From: Bruno Wolff III [mailto:bruno@wolff.to] Sent: Thursday, May 29, 2003 1:28 PM To: Carlos Cc: 'pgsql-general@postgresql.org' Subject: Re: [GENERAL] Blocking access to the database?? On Thu, May 29, 2003 at 12:29:16 -0400, Carlos <Carlos@pbsinet.com> wrote: > Hi Forum, > > Is there a way to block access to the database or terminate ongoing > connections while I carry-out updates or backups? > > Thanks in advance for your reply You could update pg_hba.conf and restart the database server. Are you sure you want to do that though? You can do consistant backups while the database is being updated by other people. And people will see a consistant view of the database if you do your updates properly.
All the DDL in postgresql except for the truncate command are transactable (and that one IS transactable in the upcoming 7.4 code right now.) What that means is you can: begin; set transaction isolation level serializable; alter table bubba rename lname to lfname; create table...; insert 10,000,000 rows; anything else you can think ifl commit; OR rollback; and either all of it goes through (i.e. commit,) or none does (i.e. rollback). Yes, one can do a SQL dump while users are modifying data. If you do: pg_dump dbname >dbname.sql you will have a complete and coherent backup of your database as it existed right at the start of the dump, and will see none of the changes made since then. It's all in the docs, here: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=backup.html On Thu, 29 May 2003, Carlos Oliva wrote: > If I had to reorganize the database, I would prefer to terminate the > connections. Any way to do that without having to restart the db > server? Could I kill the PID's for the users (Linux RedHat) > > In order to modify a user's permissions, must I restart the db server? > > Can one do an SQL-dump while users are modifying the data? > > -----Original Message----- > From: Bruno Wolff III [mailto:bruno@wolff.to] > Sent: Thursday, May 29, 2003 1:28 PM > To: Carlos > Cc: 'pgsql-general@postgresql.org' > Subject: Re: [GENERAL] Blocking access to the database?? > > > On Thu, May 29, 2003 at 12:29:16 -0400, > Carlos <Carlos@pbsinet.com> wrote: > > Hi Forum, > > > > Is there a way to block access to the database or terminate ongoing > > connections while I carry-out updates or backups? > > > > Thanks in advance for your reply > > You could update pg_hba.conf and restart the database server. > > Are you sure you want to do that though? You can do consistant backups > while the database is being updated by other people. And people will see > a consistant view of the database if you do your updates properly. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Thanks Scott, This may be the answer to what I was looking for. I was concerned about having users modifying the data in the database while I am changing the structure of the database and before I update the client programs . In my client-server application, the users interact with the database via programs that reside in their PC's and the programs get updated only after they re-start their application. -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Thursday, May 29, 2003 4:31 PM To: Carlos Cc: pgsql-general@postgresql.org Subject: Re: FW: [GENERAL] Blocking access to the database?? All the DDL in postgresql except for the truncate command are transactable (and that one IS transactable in the upcoming 7.4 code right now.) What that means is you can: begin; set transaction isolation level serializable; alter table bubba rename lname to lfname; create table...; insert 10,000,000 rows; anything else you can think ifl commit; OR rollback; and either all of it goes through (i.e. commit,) or none does (i.e. rollback). Yes, one can do a SQL dump while users are modifying data. If you do: pg_dump dbname >dbname.sql you will have a complete and coherent backup of your database as it existed right at the start of the dump, and will see none of the changes made since then. It's all in the docs, here: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=backup.h tml On Thu, 29 May 2003, Carlos Oliva wrote: > If I had to reorganize the database, I would prefer to terminate the > connections. Any way to do that without having to restart the db > server? Could I kill the PID's for the users (Linux RedHat) > > In order to modify a user's permissions, must I restart the db server? > > Can one do an SQL-dump while users are modifying the data? > > -----Original Message----- > From: Bruno Wolff III [mailto:bruno@wolff.to] > Sent: Thursday, May 29, 2003 1:28 PM > To: Carlos > Cc: 'pgsql-general@postgresql.org' > Subject: Re: [GENERAL] Blocking access to the database?? > > > On Thu, May 29, 2003 at 12:29:16 -0400, > Carlos <Carlos@pbsinet.com> wrote: > > Hi Forum, > > > > Is there a way to block access to the database or terminate ongoing > > connections while I carry-out updates or backups? > > > > Thanks in advance for your reply > > You could update pg_hba.conf and restart the database server. > > Are you sure you want to do that though? You can do consistant backups > while the database is being updated by other people. And people will > see a consistant view of the database if you do your updates properly. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >