Обсуждение: Can't Use DB As Template - accessed by other users error

since i'm having very strange problems with my
database, i want to create a new db and use the
problem db as a template.  this allows me to keep th
eproblem db in an unaltered state in case someone
needs to review it.

problem is, i get an error message:

"ERROR: source database "db" is being accessed by
other users".

i'm stuck.  can anyone help?



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Take a look at pg_stat_activity for connections to your db database. If
there are connections to "db", then you can't use it as a template for
your new database. If you get rid of these connections, you'll be able
to use db as a template for your new database.


Tim

On Tue, May 03, 2005 at 10:17:49AM -0700, operationsengineer1@yahoo.com wrote:
> since i'm having very strange problems with my
> database, i want to create a new db and use the
> problem db as a template.  this allows me to keep th
> eproblem db in an unaltered state in case someone
> needs to review it.
>
> problem is, i get an error message:
>
> "ERROR: source database "db" is being accessed by
> other users".
>
> i'm stuck.  can anyone help?
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

tim,

i spent about 30 minutes searching for pg_stat
information (manual, online, newsgroups) and couldn't
find out how to use it.  i tried "select pg_stat"
while logged into my db in psql, but nothing printed
to the screen.

while i'm at it, i have no clue how to get rid of
connections, if any are eventually found to be
present.

--- Tim Goodaire <tgoodaire@linux.ca> wrote:
> Take a look at pg_stat_activity for connections to
> your db database. If
> there are connections to "db", then you can't use it
> as a template for
> your new database. If you get rid of these
> connections, you'll be able
> to use db as a template for your new database.
>
>
> Tim
>
> On Tue, May 03, 2005 at 10:17:49AM -0700,
> operationsengineer1@yahoo.com wrote:
> > since i'm having very strange problems with my
> > database, i want to create a new db and use the
> > problem db as a template.  this allows me to keep
> th
> > eproblem db in an unaltered state in case someone
> > needs to review it.
> >
> > problem is, i get an error message:
> >
> > "ERROR: source database "db" is being accessed by
> > other users".
> >
> > i'm stuck.  can anyone help?


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Is there some way to extract the text of a previously defined stored
procedure from the database? (I just managed to clobber one of my files which
contained a couple of my plpgsql functions in it.).

Thanks,
Celia McInnis

Hi,

On 5/3/05, operationsengineer1 <operationsengineer1@yahoo.com> wrote:
> i spent about 30 minutes searching for pg_stat
> information (manual, online, newsgroups) and couldn't
> find out how to use it.  i tried "select pg_stat"
> while logged into my db in psql, but nothing printed
> to the screen.

Himm, Tim seems to be mentioning about pg_stat_activity, not pg_stat:

On 5/3/05, Tim Goodaire <tgoodaire@linux.ca> wrote:
> Take a look at pg_stat_activity for connections to your db database.

On 5/3/05, operationsengineer1 <operationsengineer1@yahoo.com> wrote:
> while i'm at it, i have no clue how to get rid of
> connections, if any are eventually found to be
> present.

[I'm not very sure about this would be the right way to achieve what
you want, but]
List the users, their PIDs and which query they currently on:

=> SELECT procpid, usename, current_query FROM pg_stat_activity;

Check users and their queries. If the "kill [-9] procpid" will be
harmful for that user, just warn him/her. Or try some other
combinations of this messy idea.

Regards.

"Celia McInnis" <celia@drmath.ca> writes:
> Is there some way to extract the text of a previously defined stored
> procedure from the database? (I just managed to clobber one of my files which
> contained a couple of my plpgsql functions in it.).

"pg_dump -s" is your best bet.

            regards, tom lane

What's the easiest way to drop a composite type definition or a table if you
aren't guaranteed that it exists?

I need to do this quite frequently from stored procedures and get booted out
of the stored procedure if I try to DROP something which doesn't currently
exist.

Thanks,
Celia McInnis

> [I'm not very sure about this would be the right way to achieve what
> you want, but]
> List the users, their PIDs and which query they currently on:
>
> => SELECT procpid, usename, current_query FROM pg_stat_activity;
>
> Check users and their queries. If the "kill [-9] procpid" will be
> harmful for that user, just warn him/her. Or try some other
> combinations of this messy idea.

The above query will get you a list of PIDs that are accessing the db.
At the command line, as user postgres, do a kill -2 on those PIDs. DO
NOT kill -9 them.

If you don't find any entries in the pg_stat_activity table, then it may
be the case that you're not monitoring it. In that case, again as user
postgres, do a ps xww to find connections and then kill them as
described previously.

All of the above assumes that you're running on a unix platform.

Drew