How to kill all sessions in Postgres so that they stay dead.

No Comments
Postgres Connections

Have you ever tried to drop or rename a postgres database or even create another database from one as a template, only to be confronted by the dreaded message:

postgres=# create database test with template a_database;
ERROR: source database “a_database” is being accessed by other users
DETAIL: There are 40 other sessions using the database.

So, we kill those sessions off with something like the below SQL that will kill all sessions that are connected to that database:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()pg_stat_activity.datname = ‘TARGET_DB’
AND pg_stat_activity.datname = ‘A_DATABASE’;

Job done, now we can create our database… But wait!

postgres=# create database test with template a_database;
ERROR: source database “a_database” is being accessed by other users
DETAIL: There are 40 other sessions using the database.

Someone has beaten us too it and reconnected!

If you are like me, the only logical solution is to try to do the above statements really really fast. Unfortunately, I normally lose, so I need a way to stop sessions from reconnecting to the database that doesn’t involve changing config files etc.

Fortunately, the simple solution is to disallow all connections to that database and we can do it with this line:

update pg_database set datallowconn = false where datname = ‘a_database’;

Once you are finished, you just need to set it back to True and the database will be open for connections again.

Categories: PostgreSQL

How to make a Decision

How to Make a Decision

How to Make a Decision

I am slightly embarrassed to admit that I have often avoided making decisions throughout my life. That’s not to say that I haven’t made things happen but I have often avoided putting my foot down and choosing a path one way or the other, preferring instead to allow circumstances to make the decision myself. That’s not to say that I haven’t subconsciously pushed circumstances one way or another, but it’s not the same and does not give you the same benefits as being decisive and telling the world what you want.

The thing is that an argument could be made that the whole point of life is to make decisions (if we take any spiritual questions out of the equation). The biological definition of life is that ‘life is irritable’ meaning that the environment does something and life does something back. This is true for all life – a plant decides on some level to move towards the light and if it were to stop doing it, it would die. The same is true for us on a much more complex level. If we were to stop making decisions, we would not get up, eat or drink and we would soon die.

Read More…

Categories: Self Improvement

How to live 10 Years Longer by Playing Games

Live 10 years LongerPlus be happier and have fewer regrets

“I’m a gamer so I like to have goals. I like special missions and secret objectives, so here’s my special mission for this talk. I’m going to try to increase the lifespan of everyone in this room by seven and a half minutes.”
– Jane McGonigal

Jane Mcdonigal gave a talk at TED recently that I loved. Its backed by some real research and gives us a way to not only live longer but to enjoy life while we live it and to avoid the top 5 deathbed regrets which are:

Top 5 deathbed regrets: Read More…

Categories: Self Improvement Tags: Tags:

How to instantly be more powerful and feel better about yourself.

Body LanguageThe cheaters guide to being more powerful, confident and becoming the person you wish you could be (and living longer as a bonus).Its quite a promise but you will easily get that and a lot more from this short post and video.

A word of warning though is that you get absolutely no benefit whatsoever from this until you physically do it. On the plus side there is nothing easier to test and you can immediately feel the benefits. What even better is that the more that you do it, the greater the benefit that you get and the more ingrained the changes will become.
Read More…

Categories: Self Improvement Tags: Tags:

What is an Oracle Service

Oracle services were a feature introduced in Oracle 10g. Their function is to simplify workload management by allowing you to group applications that share traits such as thresholds, priorities and attributes.

The Oracle database is presented as a service and so you always have at least one service running. It is good practice to create at least one more service for application workloads and not to use the default Oracle service as it cannot by changed and will always allow connections.

One instance of a RAC database can support multiplke services and one service can spoan multiple instances. One service can support many applications, one or even a subset of one appliation.

You can create a service to accommodate batch users and another to deal with online users. Alternatively, you could assign a service to handle a particular application, application type or parts of an application etc.

It is recommended that you use a service to connect applications to the database and you do this by specifying the service name in the application’s connection string.

The resource manager is tightly integrated with services and you can control how and when services can run in an instance with it. It also allows scheduler jobs to tun as a service which allows you greater flexibility about how and when they run.