Posts Tagged PostgreSQL
As a Database Developer or Database Administrator, it becomes increasingly unlikely that you will work with only one platform.
It’s quite useful to have one single tool to handle multiple different database platforms. And that’s exactly the ambition of DbVisualizer.
As a hypothetical scenario, let’s assume you are a database admin who works on a project to migrate from Oracle to EDB Postgres and Exasol.
The goal might be to replace the corporate Oracle database landscape, moving the OLTP part to EDB Postgres and the DWH / Analytics part to Exasol.
Instead of having to switch constantly between say SQL Developer, psql and EXAplus, a more efficient approach would be using DbVisualizer for all three.
I created one connection for each of the three databases here for my demo:Now let’s see if statements I do in Oracle also work in EDB Postgres and in Exasol:
Works the same for all three! The convenient thing here is that I just had to select the Database Connection from the pull down menu while leaving the statement as it is. No need to copy & paste even.
What about schemas and tables?
In EDB, I need to create a schema accordingly:
In Exasol, schema and table can be created in the same way:
Notice that the data types got silently translated into the proper Exasol data types:
There is no DBA_TABLES in Exasol, though:
Of course, there’s much more to check and test upon migration, but I think you got an idea how a universal SQL Client like DbVisualizer might help for such purposes.
Check out also this about 2 Minutes video clip that we recorded about DbVisualizer as part of our Exasol Guidance series:
In PostgreSQL, you can cancel problem statements or terminate offending sessions remotely with PG_CANCEL_BACKEND and PG_TERMINATE_BACKEND. This article shows how you can do it with working examples.
edb=# select current_database,current_user; current_database | current_user ------------------+-------------- edb | enterprisedb (1 row)
I’m connected as superuser. The demo is done with EDB Postgres Advanced Server 10.1.5 but the shown technique should work the same with other Postgres distributions and older versions. First I create a demo user:
edb=# create role adam password 'adam' login; CREATE ROLE edb=# grant connect on database edb to adam; GRANT
Now opening another session with that new user:
-bash-4.2$ psql -U adam Password for user adam: psql.bin (10.1.5) Type "help" for help. edb=> begin edb$> loop edb$> null; edb$> end loop; edb$> end;
That session burns CPU now in an endless loop. Back to the superuser session:
edb=# select pid,usename,query edb-# from pg_catalog.pg_stat_activity where datname='edb'; pid | usename | query -------+--------------+------------------------------------------------------- 14346 | adam | begin + | | loop + | | null; + | | end loop; + | | end; 5517 | enterprisedb | select pid,usename,query + | | from pg_catalog.pg_stat_activity where datname='edb'; (2 rows)
This cancels the SQL statement of one session:
edb=# select pg_cancel_backend(14346); pg_cancel_backend ------------------- t (1 row)
The session that was doing the endless loop gets this output:
ERROR: canceling statement due to user request CONTEXT: edb-spl function inline_code_block line 3 at NULL
This disconnects a single session:
edb=# select pg_terminate_backend(14346); pg_terminate_backend ---------------------- t (1 row)
The disconnected session gets this output after trying to do anything:
edb=> \d FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
This way you cancel all SQL statements of a certain user:
edb=# select pg_cancel_backend(pid) from pg_stat_activity where usename='adam';
This way you disconnect all sessions of a certain user:
edb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='adam';
In the unlikely event that your end users are connected with psql, it will try to reconnect them. That’s the background of the above listed output “… Attempting reset: Succeeded.” Means the session got reconnected. If you want to prevent that particular user from (re-)connecting, you need to do this additionally:
edb=# revoke connect on database edb from adam; REVOKE ROLE
Remember to also revoke from public if you haven’t done that already, otherwise the above revoke doesn’t show any effect:
edb=# revoke connect on database edb from public; REVOKE
Now this disconnects all sessions of one user and the above prevents new connects by that user:
edb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='adam'; pg_terminate_backend ---------------------- t (1 row)
The terminated session then gets this output:
edb=> \d FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed.
Keep in mind that the revoke is impacting the whole user, not just one particular session. In other words no session with that user can be established subsequently until you say:
edb=# grant connect on database edb to adam; GRANT ROLE
You can terminate all sessions connected to one particular database – except your own session – like this:
edb=# select pg_terminate_backend(pid) edb-# from pg_stat_activity edb-# where datname='edb' and pid<>pg_backend_pid;
Once again, this does not prevent new sessions from connecting. So either you REVOKE CONNECT on the user layer as shown above, or you do it on the database layer.
This is how normal users are prevented from connecting to the database:
edb=# alter database edb with connection limit 0; ALTER DATABASE
Superusers can still connect. The above is the equivalent to ALTER SYSTEM ENABLE RESTRICTED SESSION in Oracle. This is what normal users get now upon trying to connect:
-bash-4.2$ psql -U adam Password for user adam: psql.bin: FATAL: too many connections for database "edb"
The default of unlimited number of sessions allowed to connect can be set back like this:
edb=# alter database edb with connection limit -1; ALTER DATABASE
This prevents also superusers from connecting to a database, but you must not be connected to that database yourself:
edb=# alter database edb with allow_connections false; ERROR: cannot disallow connections for current database
So either you connect to another existing database in the cluster or you create another database temporarily:
edb=# create database dummy; CREATE DATABASE edb=# \c dummy You are now connected to database "dummy" as user "enterprisedb". dummy=# alter database edb with allow_connections false; ALTER DATABASE
Now both normal users and superusers get this output when trying to connect:
psql.bin: FATAL: database "edb" is not currently accepting connections
The default that connections to that database are allowed can be set back with this command:
dummy=# alter database edb with allow_connections true; ALTER DATABASE dummy=# \c edb You are now connected to database "edb" as user "enterprisedb". edb=# drop database dummy; DROP DATABASE
What I like especially about the shown functionality is the option to remotely cancel a particular (ongoing) statement without having to terminate the session that runs the statement. I’m not aware of a supported way to do that in Oracle. It can be done if Resource Manager has been configured appropriately beforehand, but that requires quite some effort and doesn’t work just out of the box.