Thread: 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
Re: Can't Use DB As Template - accessed by other users error
From
tgoodaire@linux.ca (Tim Goodaire)
Date:
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