Thread: stopping access to a database
I configuring 2 databases:
1/ One be on a https server, and all important data will be encrypted. The only use this
database will see are:
A/ Updates 2-3 times a day, max.
B/ Exporting a smaller set of 'unnormalized' tables for the database below.
2/ A publically accessible, (through PHP applications), database that has less secure data
on it and is not encrypted.
I would like to stop accesses to database 2, delete all records in the 2-4 tables, and reimport
new records. How would I do this as a user on the system, not as an admin?
Having just started receiving the list without actually subscribing, I didn't get the info email from the server that tells me how to unsubscribe. Can anybody please point me in the right direction? It's been a while since I've been on this list, and I don't remember the procedure from last time... Regards. Gordan
> Having just started receiving the list without actually subscribing, I
Interesting, the same thing happened to me. I figured I must've
subscribed a long time ago and recently botched a procmail rule or
something, but if this has happened to others tooo ...
--
thanks,
Will
On Tue, 2003-01-28 at 11:50, Dennis Gearon wrote: > I would like to stop accesses to database 2, delete all records in the 2-4 tables, and reimport > new records. How would I do this as a user on the system, not as an admin? How about starting a transaction, acquiring an exclusive lock on all the tables using LOCK TABLE, deleting & updating the data, and then committing the transaction? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
At 11:05 AM -0800 1/28/03, Will Lowe wrote: > > Having just started receiving the list without actually subscribing, I > >Interesting, the same thing happened to me. I figured I must've >subscribed a long time ago and recently botched a procmail rule or >something, but if this has happened to others tooo ... I had subscribed a very long time ago, and it just stopped coming. I tried unsubscribing, re-subscribing, subscribing under a different address, etc., and I still couldn't get mail. I tried even to get a hold of a human being, with no luck, so I gave up. All of a sudden, now I'm getting two messages!! -- .Michelle -------------------------- Michelle Murrain, Technology Consulting tech@murrain.net http://www.murrain.net 413-253-2874 ph 413-222-6350 cell 413-825-0288 fax AIM:pearlbear0 Y!:pearlbear9 ICQ:129250575 "A vocation is where the world's hunger & your great gladness meet." Frederick Buechner
So,
the connections that will see the new data won't be able to access data until that
transaction has finished the 'commit' stage, or will they be able to read some of the records
while the importation is partially done?
1/28/2003 9:27:59 AM, Charles Tassell <ctassell@isn.net> wrote:
>There is no need to block all access, simply use a transaction. IE:
>
>begin work;
>delete from table1;
>delete from tabl2;
>... so on ..
>.. import data ...
>commit work;
>
>Anyone who connects while the delete/import is taking place will simply
>see the old data, when you run the commit work command, all the new
>queries will see the newly imported data.
>
>
>On Tue, 2003-01-28 at 12:50, Dennis Gearon wrote:
>> I configuring 2 databases:
>>
>> 1/ One be on a https server, and all important data will be encrypted. The only use this
>> database will see are:
>> A/ Updates 2-3 times a day, max.
>> B/ Exporting a smaller set of 'unnormalized' tables for the database below.
>> 2/ A publically accessible, (through PHP applications), database that has less secure
data
>> on it and is not encrypted.
>>
>> I would like to stop accesses to database 2, delete all records in the 2-4 tables, and
reimport
>> new records. How would I do this as a user on the system, not as an admin?
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>
>
>
Is this a virus??? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gordan Bobic Sent: Tuesday, January 28, 2003 12:58 PM To: pgsql-general@postgresql.org Subject: [GENERAL] How do I unsubscribe? Having just started receiving the list without actually subscribing, I didn't get the info email from the server that tells me how to unsubscribe. Can anybody please point me in the right direction? It's been a while since I've been on this list, and I don't remember the procedure from last time... Regards. Gordan ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Ooooooooohhhhhhhhhhh,
That would work! I didn't know I could get an exclusive lock on tables in a transaction,
I thought it was just for rows, (the opposite of MySQL). This is good, and 'back to the
manual' for more reading!
1/28/2003 12:22:50 PM, Neil Conway <neilc@samurai.com> wrote:
>On Tue, 2003-01-28 at 11:50, Dennis Gearon wrote:
>> I would like to stop accesses to database 2, delete all records in the 2-4 tables, and
reimport
>> new records. How would I do this as a user on the system, not as an admin?
>
>How about starting a transaction, acquiring an exclusive lock on all the
>tables using LOCK TABLE, deleting & updating the data, and then
>committing the transaction?
>
>Cheers,
>
>Neil
>--
>Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
>
>
>
>
You know,
There are three big threads going on in the list now:
A/ Tablespaces
B/ Backing up the database
C/ Getting people unsubscribed because of OLD DATA being restored.
Methinks that 'C' demonstrates the need for 'B'. If the database for subscribers had been
regularly backed up or was part of a journaling system, the subscriber database would not
have such OLD DATA in it. Having the database capable of having a backup EASILY and
AUTOMATICALLY, which is what 'B' is about, would have prevented 'C'
1/28/2003 11:05:16 AM, Will Lowe <harpo@thebackrow.net> wrote:
>> Having just started receiving the list without actually subscribing, I
>
>Interesting, the same thing happened to me. I figured I must've
>subscribed a long time ago and recently botched a procmail rule or
>something, but if this has happened to others tooo ...
>
>--
>
> thanks,
>
> Will
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
> So, > the connections that will see the new data won't be able to > access data until that > transaction has finished the 'commit' stage, or will they be able > to read some of the records > while the importation is partially done? The former. Other transations won't see the data and also because you went: LOCK TABLE IN ACCESS EXCLUSIVE MODE or something, they won't even be able to access the tables... Chris
No, this is not a virus, the email database for the mailing lists got reset with a bunch of old accounts in it. On Tue, 28 Jan 2003, Terri Lerose wrote: > Is this a virus??? > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gordan Bobic > Sent: Tuesday, January 28, 2003 12:58 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] How do I unsubscribe? > > Having just started receiving the list without actually subscribing, I > didn't get the info email from the server that tells me how to > unsubscribe. Can anybody please point me in the right direction? It's > been > a while since I've been on this list, and I don't remember the procedure
Why do you even need to lock access to the tables??? Just fire up a transaction, delete everything from the tables and import the new data and then commit. Other users willjust see the old data right up until you commit. Chris > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dennis Gearon > Sent: Wednesday, 29 January 2003 8:11 AM > To: Neil Conway > Cc: postgres list > Subject: Re: [GENERAL] stopping access to a database > > > Ooooooooohhhhhhhhhhh, > That would work! I didn't know I could get an exclusive > lock on tables in a transaction, > I thought it was just for rows, (the opposite of MySQL). This is > good, and 'back to the > manual' for more reading! > > 1/28/2003 12:22:50 PM, Neil Conway <neilc@samurai.com> wrote: > > >On Tue, 2003-01-28 at 11:50, Dennis Gearon wrote: > >> I would like to stop accesses to database 2, delete all > records in the 2-4 tables, and > reimport > >> new records. How would I do this as a user on the system, not > as an admin? > > > >How about starting a transaction, acquiring an exclusive lock on all the > >tables using LOCK TABLE, deleting & updating the data, and then > >committing the transaction? > > > >Cheers, > > > >Neil > >-- > >Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
There is no need to block all access, simply use a transaction. IE: begin work; delete from table1; delete from tabl2; ... so on .. .. import data ... commit work; Anyone who connects while the delete/import is taking place will simply see the old data, when you run the commit work command, all the new queries will see the newly imported data. On Tue, 2003-01-28 at 12:50, Dennis Gearon wrote: > I configuring 2 databases: > > 1/ One be on a https server, and all important data will be encrypted. The only use this > database will see are: > A/ Updates 2-3 times a day, max. > B/ Exporting a smaller set of 'unnormalized' tables for the database below. > 2/ A publically accessible, (through PHP applications), database that has less secure data > on it and is not encrypted. > > I would like to stop accesses to database 2, delete all records in the 2-4 tables, and reimport > new records. How would I do this as a user on the system, not as an admin? > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Count me in on that. I get mail without subscribing. Will Lowe wrote: >>Having just started receiving the list without actually subscribing, I >> > > Interesting, the same thing happened to me. I figured I must've > subscribed a long time ago and recently botched a procmail rule or > something, but if this has happened to others tooo ... > > -- > > thanks, > > Will > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > -- Lalo Castro Programmer/Analyst McHenry Library (831) 459-5208
Many moons ago, I subscribed. I had long since unsubscribed or set my
profile up to not send messages. I suspect that the list software somehow
munged a database or something.
Is there some demunging facility?
-- Matt
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Will Lowe
Sent: Tuesday, January 28, 2003 2:05 PM
To: Gordan Bobic
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I unsubscribe?
> Having just started receiving the list without actually subscribing, I
Interesting, the same thing happened to me. I figured I must've subscribed a
long time ago and recently botched a procmail rule or something, but if this
has happened to others tooo ...
--
thanks,
Will
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your message
can get through to the mailing list cleanly
Dennis Gearon <gearond@cvc.net> writes on Wed, 29 Jan 2003 01:08:19 +0100 (MET): > You know, > There are three big threads going on in the list now: > > A/ Tablespaces > B/ Backing up the database > C/ Getting people unsubscribed because of OLD DATA being restored. Don't forget to unsubscribe me. > Having the database capable of having a backup EASILY and > AUTOMATICALLY, which is what 'B' is about, would have prevented 'C' Database snapshots? Masi
Dennis: You gotta ask yourself -- "how long is the delete / insert going to take ???" -- all of about 3 seconds probably.... I'd just do it in a transaction because you said that database 2 is "publicly accessible"... What happens if Joe Public is trying to get some of that public info and you have the systemn locked... "Dennis Gearon" <gearond@cvc.net> wrote in message news:PL54ZWTRID64QP95PKD952WTXWQLRLRM.3e36b4bb@cal-lab... > I configuring 2 databases: > > 1/ One be on a https server, and all important data will be encrypted. The only use this > database will see are: > A/ Updates 2-3 times a day, max. > B/ Exporting a smaller set of 'unnormalized' tables for the database below. > 2/ A publically accessible, (through PHP applications), database that has less secure data > on it and is not encrypted. > > I would like to stop accesses to database 2, delete all records in the 2-4 tables, and reimport > new records. How would I do this as a user on the system, not as an admin? > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org