pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedREVOKE command not working as expected
7+ messages / 4 participants
[nested] [flat]
* REVOKE command not working as expected
@ 2013-11-12 14:58 Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:01 ` Re: REVOKE command not working as expected Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Guillermo Carrasco @ 2013-11-12 14:58 UTC (permalink / raw)
To: pgsql-admin
Hi everyone,
We have a small script that we use to replicate our database from our
production server to a stage one, where we do the development. More than a
"replication" is a copy, so what it does is deletes everything on stage and
then copies everything from production to stage. We do this at the
beginning of the week to keep both databases in synch whilst during the
week the developers can experiment. Here is the script:
https://github.com/guillermo-carrasco/scilifelab/blob/master/scripts/dbsync.sh
The problem is that sometimes (apparently random), even after having
invoked EVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;, when the scripts
starts dumping, it fails because it says that there are some active
connections.
I do not understand what's going on here, theoretically, if I prevent
connections and after that I revoke all the existing ones, I shouldn't see
that problem, right?
Any help is very appreciated.
Thanks in advance!
--
------------------------------------------------------------------------
Guillermo Carrasco Hernández, Computer Scientist
GitHub: https://github.com/guillermo-carrasco
LinkedIn: http://linkd.in/XeFUSB<http://www.linkedin.com/pub/guillermo-carrasco-hern%C3%A1ndez/43/760/220;
Personal blog: http://mussolblog.wordpress.com/
------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE command not working as expected
2013-11-12 14:58 REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
@ 2013-11-12 15:01 ` Scott Marlowe <scott.marlowe@gmail.com>
2013-11-12 15:02 ` Re: REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:13 ` Re: REVOKE command not working as expected Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Scott Marlowe @ 2013-11-12 15:01 UTC (permalink / raw)
To: Guillermo Carrasco <guillermo.carrasco@scilifelab.se>; +Cc: pgsql-admin
On Tue, Nov 12, 2013 at 7:58 AM, Guillermo Carrasco
<guillermo.carrasco@scilifelab.se> wrote:
> Hi everyone,
>
> The problem is that sometimes (apparently random), even after having invoked
> EVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;, when the scripts starts
> dumping, it fails because it says that there are some active connections.
Revoking connection does not disconnect currently connected clients.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE command not working as expected
2013-11-12 14:58 REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:01 ` Re: REVOKE command not working as expected Scott Marlowe <scott.marlowe@gmail.com>
@ 2013-11-12 15:02 ` Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:19 ` Re: REVOKE command not working as expected Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 7+ messages in thread
From: Guillermo Carrasco @ 2013-11-12 15:02 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
Hi,
Thanks for your answer, I know, that's why we have this statement:
https://github.com/guillermo-carrasco/scilifelab/blob/master/scripts/dbsync.sh#L20
On Tue, Nov 12, 2013 at 4:01 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Tue, Nov 12, 2013 at 7:58 AM, Guillermo Carrasco
> <guillermo.carrasco@scilifelab.se> wrote:
> > Hi everyone,
> >
> > The problem is that sometimes (apparently random), even after having
> invoked
> > EVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;, when the scripts starts
> > dumping, it fails because it says that there are some active connections.
>
> Revoking connection does not disconnect currently connected clients.
>
--
------------------------------------------------------------------------
Guillermo Carrasco Hernández, Computer Scientist
GitHub: https://github.com/guillermo-carrasco
LinkedIn: http://linkd.in/XeFUSB<http://www.linkedin.com/pub/guillermo-carrasco-hern%C3%A1ndez/43/760/220;
Personal blog: http://mussolblog.wordpress.com/
------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE command not working as expected
2013-11-12 14:58 REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:01 ` Re: REVOKE command not working as expected Scott Marlowe <scott.marlowe@gmail.com>
2013-11-12 15:02 ` Re: REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
@ 2013-11-12 15:19 ` Scott Ribe <scott_ribe@elevated-dev.com>
2013-11-13 07:42 ` Re: REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
0 siblings, 1 reply; 7+ messages in thread
From: Scott Ribe @ 2013-11-12 15:19 UTC (permalink / raw)
To: Guillermo Carrasco <guillermo.carrasco@scilifelab.se>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
Why not just shut down the staging server, pg_start_backup on production, and rsync the db???
On Nov 12, 2013, at 8:02 AM, Guillermo Carrasco <guillermo.carrasco@scilifelab.se> wrote:
> Thanks for your answer, I know, that's why we have this statement: https://github.com/guillermo-carrasco/scilifelab/blob/master/scripts/dbsync.sh#L20
>
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE command not working as expected
2013-11-12 14:58 REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:01 ` Re: REVOKE command not working as expected Scott Marlowe <scott.marlowe@gmail.com>
2013-11-12 15:02 ` Re: REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:19 ` Re: REVOKE command not working as expected Scott Ribe <scott_ribe@elevated-dev.com>
@ 2013-11-13 07:42 ` Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-13 14:27 ` Re: REVOKE command not working as expected Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 7+ messages in thread
From: Guillermo Carrasco @ 2013-11-13 07:42 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
Hi Scott,
We want to make an exact copy of production, even if someone have made
changes in stage, we want to remove all the changes and leave stage as an
exact copy of production, will pg_start_backup do the work?
On Tue, Nov 12, 2013 at 4:19 PM, Scott Ribe <scott_ribe@elevated-dev.com>wrote:
> Why not just shut down the staging server, pg_start_backup on production,
> and rsync the db???
>
> On Nov 12, 2013, at 8:02 AM, Guillermo Carrasco <
> guillermo.carrasco@scilifelab.se> wrote:
>
> > Thanks for your answer, I know, that's why we have this statement:
> https://github.com/guillermo-carrasco/scilifelab/blob/master/scripts/dbsync.sh#L20
> >
>
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>
>
--
------------------------------------------------------------------------
Guillermo Carrasco Hernández, Computer Scientist
GitHub: https://github.com/guillermo-carrasco
LinkedIn: http://linkd.in/XeFUSB<http://www.linkedin.com/pub/guillermo-carrasco-hern%C3%A1ndez/43/760/220;
Personal blog: http://mussolblog.wordpress.com/
------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE command not working as expected
2013-11-12 14:58 REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:01 ` Re: REVOKE command not working as expected Scott Marlowe <scott.marlowe@gmail.com>
2013-11-12 15:02 ` Re: REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:19 ` Re: REVOKE command not working as expected Scott Ribe <scott_ribe@elevated-dev.com>
2013-11-13 07:42 ` Re: REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
@ 2013-11-13 14:27 ` Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Ribe @ 2013-11-13 14:27 UTC (permalink / raw)
To: Guillermo Carrasco <guillermo.carrasco@scilifelab.se>; +Cc: pgsql-admin
On Nov 13, 2013, at 12:42 AM, Guillermo Carrasco <guillermo.carrasco@scilifelab.se> wrote:
> We want to make an exact copy of production, even if someone have made changes in stage, we want to remove all the changes and leave stage as an exact copy of production, will pg_start_backup do the work?
Copying the files pg uses does the work ;-)
pg_start_backup just enables you to copy a live database and get a consistent result.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE command not working as expected
2013-11-12 14:58 REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:01 ` Re: REVOKE command not working as expected Scott Marlowe <scott.marlowe@gmail.com>
@ 2013-11-12 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2013-11-12 15:13 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Guillermo Carrasco <guillermo.carrasco@scilifelab.se>; pgsql-admin
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Tue, Nov 12, 2013 at 7:58 AM, Guillermo Carrasco
> <guillermo.carrasco@scilifelab.se> wrote:
>> The problem is that sometimes (apparently random), even after having invoked
>> EVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;, when the scripts starts
>> dumping, it fails because it says that there are some active connections.
> Revoking connection does not disconnect currently connected clients.
My money is on autovacuum.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2013-11-13 14:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-12 14:58 REVOKE command not working as expected Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:01 ` Scott Marlowe <scott.marlowe@gmail.com>
2013-11-12 15:02 ` Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-12 15:19 ` Scott Ribe <scott_ribe@elevated-dev.com>
2013-11-13 07:42 ` Guillermo Carrasco <guillermo.carrasco@scilifelab.se>
2013-11-13 14:27 ` Scott Ribe <scott_ribe@elevated-dev.com>
2013-11-12 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox