How to select only duplicate records?

This is a guideline to select:

First ways:

Select State FROM Area
GROUP BY State
Having COUNT(*) > 1

Second ways:

 
SELECT DISTINCT a1.State
FROM AREA a1
JOIN AREA a2
  ON a1.AreaId != a2.AreaId  -- assume there is a Key to join on
  AND a1.State = a2.State    -- and such that different Areas with same State

Postgresql constraint

This is an sql statement to add new foreign key on table:

ALTER TABLE my_table ADD CONSTRAINT my_fk FOREIGN KEY (my_field) 
REFERENCES my_foreign_table ON DELETE CASCADE ON UPDATE CASCADE;

Please enjoy your work!

PGsql : Grant every single right to a user on a schema

GRANT ALL PRIVILEGES ON                  SCHEMA schema_name TO role_name;
GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA schema_name TO role_name;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO role_name;

Recovering from a lost PostgreSQL password.

=> psql
Password:
psql: FATAL: password authentication failed for user "testusers"

 ow what?
If you have access to shell account on the machine PostgreSQL is running, and your shell works as the same user as Postgres itself, or root – solution is easy.
Find your pg_hba.conf file. It might be in many files so try:

• $ locate pg_hba.conf
• find /var/lib/ -type f -name pg_hba.conf
• find /etc -type f -name pg_hba.conf
• find / -type f -name pg_hba.conf

Of course last option is your last resort – it will take a long time.
When you'll find it, it might contain something like this:

=> cat /some/location/pg_hba.conf
local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5

There might be more lines like these, there might be comments of blank lines.
Now. Edit the file, and at the beginning of it put:
local all all trust
or (depending on your paranoia):
local all postgres ident
And restart your PostgreSQL (usually something like /etc/init.d/postgres restart).
Afterwards you should be able to connect to Postgres as postgres user without password.
You should note, that if you have choosen the option with “ident" you will be able to connect without password only from shell account named “postgres".
When you'll connect issue alter user command:
=> psql
...
# alter user postgres with password 'new password, that i will never forget';
ALTER ROLE
After the change, remove this extra-added line from pg_hba.conf, restart Postgres, and that's all. You should have the access back.
 

PostreSQL 9.3 Streaming Replication Howto / Tutorial

Introduction

This tutorial will walk through configuring master/slave servers for postgresql 9.3 using streaming replication.

Assumptions

• You are not trying to configure an existing data set (i.e. fresh install)
• You are running Ubuntu 14.04 LTS
• You have run apt-get update ; apt-get upgrade
• You have two servers to use as a master and a slave
• $HOME and ~/ for the user postgres are set to /var/lib/postgresql
• We will work out of /var/lib/postgresql for all/most commands
• We are not using archiving

Also important to note is that all actions should be run as postgres user, NOT ROOT.

su - postgres
cd /var/lib/postgresql

Hosts/servers

• master (db1 / 192.168.1.10)
• slave (db2 / 192.168.20)

Directories

• Data (/var/lib/postgresql/9.3/main)
• Configs (/etc/postgresql/9.3/main)

Step 1 - Stop pgsql

First make sure postgresql is not running on both servers (master and slave).

service postgresql stop

Step 2 - Remove existing data

On both servers, we will clear any existing data in the data directory (see above).

rm -rf /var/lib/postgresql/9.3/main/*

Step 3 - Configure pg_hba.conf on master

To allow our slave to connect to our master, we will need to make sure the pg_hba.conf has an entry for our replicator role to connect.

vi /etc/postgresql/9.3/main/pg_hba.conf

Now what we want to do is add two entries (ssl and non-ssl) for the user/role replicator

host    replication    replicator    192.168.1.20/32    md5
hostssl    replication    replicator    192.168.1.20/32    md5

This now allows user replicator from IP 192.168.1.20 using md5 password method.

Step 4 - Configure postgresql.conf on master

Next, we will update the postgresql.conf file and change the settings required to get streaming replication working.

vi /etc/postgresql/9.3/main/postgresql.conf

There are X settings to change, you can make them look like the following:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 8
checkpoint_segments = 8

Be sure to check the last setting, checkpoint_segments, as this may have been manually updated if you use the pgtune tool.

Step 5 - Initialize the database on master

Now we are ready to initialize the database directory on the master server. This will create all the necessary files/directories needed to run normally.

initdb /var/lib/postgresql/9.3/main

At this point we can start postgresql on the master server.

service postgresql start

We should now have a fresh instance of postgresql up and running.

Step 6 - Add replicator user to master

We now need to add our replicator user which will be used to connect and read the replication pseudo-table.

psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'your-password';"

Now that our master server is primed and ready, we can move onto the slave.

Step 7 - Configure postgresql.conf on the slave

At this point I would add a note that the following worked for me, but based on the comments in the config file, some are only relevant to the master and may not need to be set, so feel free to try without them.

vi /etc/postgresql/9.3/main/postgresql.conf

Now change these settings, and again it's possible that only the hot_standby option need be set.

listen_addresses = '*'
hot_standby = on
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 8
checkpoint_segments = 8

Step 8 - Copy data from master to slave

On the slave, we will use the pg_basebackup utility to get a copy of the master's data. This will prompt for a password which is the one you configured in Step 6 when you created the replicator user.

pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/9.3/main -U replicator -P -v -x

This should complete successfully, if you see any errors then you will need to try to figure them out before continuing. Now we need to create a recovery.conf file and we will link it in the data directory.

vi /etc/postgresql/9.3/main/recovery.conf
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=your-password'
trigger_file = '/var/lib/postgresql/9.3/main/failover'
standby_mode = 'on'

There are a couple of other options you can specify for the primary_conninfo (and probably more).

• keepalives_idle=60
• sslmode=require

Step 9 - Starting the slave

We should now have our fully loaded slave with a copy of the master's data ready to go. We can now start postgresql on the slave.

service postgresql start

Finished

That wraps up this tutorial. Part of my learning curve was getting stuck with trying to figure out archiving and how that tied into streaming replication, which it seems they are different solutions. I struggled for hours trying to get this to work before finally disabling the archive options.

Advisory Lock Functions in PostgreSQL

pg_advisory_lock locks an application-defined resource, which can be identified either by a single 64-bit key value or two 32-bit key values (note that these two key spaces do not overlap). If another session already holds a lock on the same resource identifier, this function will wait until the resource becomes available. The lock is exclusive. Multiple lock requests stack, so that if the same resource is locked three times it must then be unlocked three times to be released for other sessions' use.

pg_advisory_lock_shared works the same as pg_advisory_lock, except the lock can be shared with other sessions requesting shared locks. Only would-be exclusive lockers are locked out.

pg_try_advisory_lock is similar to pg_advisory_lock, except the function will not wait for the lock to become available. It will either obtain the lock immediately and return true, or return false if the lock cannot be acquired immediately.

pg_try_advisory_lock_shared works the same as pg_try_advisory_lock, except it attempts to acquire a shared rather than an exclusive lock.

pg_advisory_unlock will release a previously-acquired exclusive session level advisory lock. It returns true if the lock is successfully released. If the lock was not held, it will return false, and in addition, an SQL warning will be reported by the server.

pg_advisory_unlock_shared works the same as pg_advisory_unlock, except it releases a shared session level advisory lock.

pg_advisory_unlock_all will release all session level advisory locks held by the current session. (This function is implicitly invoked at session end, even if the client disconnects ungracefully.)

pg_advisory_xact_lock works the same as pg_advisory_lock, except the lock is automatically released at the end of the current transaction and cannot be released explicitly.

pg_advisory_xact_lock_shared works the same as pg_advisory_lock_shared, except the lock is automatically released at the end of the current transaction and cannot be released explicitly.

pg_try_advisory_xact_lock works the same as pg_try_advisory_lock, except the lock, if acquired, is automatically released at the end of the current transaction and cannot be released explicitly.

pg_try_advisory_xact_lock_shared works the same as pg_try_advisory_lock_shared, except the lock, if acquired, is automatically released at the end of the current transaction and cannot be released explicitly.

Force drop db while others may be connected in postgresql

You can't drop postgres database while clients are connected to it. Quite robust way to work around it, is

1. Make sure noone can connect to this database
   update pg_database set datallowconn = 'false' where datname = 'mydb';
2. Force disconnection of all clients connected to this database.
   For postgres < 9.2:
   SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'mydb';
   for postgres versions >= 9.2 change procpid to pid:
   SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';
3. Drop it
   DROP DATABASE mydb;

Steps 1 and 2 require superuser privileges, step 3 requires database owner privilege.
You can't do it all using only dropdb utility - which is a simple wrapper around DROP DATABASE server query.

Insert all values of a table into another table in SQL

The insert statement actually has a syntax for doing just that. It's a lot easier if you specify the column names rather than selecting "*" though:

INSERT INTO new_table (Foo, Bar, Fizz, Buzz) SELECT Foo, Bar, Fizz, Buzz FROM initial_table -- optionally WHERE ...

I'd better clarify this because for some reason this post is getting a few down-votes.
The INSERT INTO ... SELECT FROM syntax is for when the table you're inserting into ("new_table" in my example above) already exists. As others have said, the SELECT ... INTO syntax is for when you want to create the new table as part of the command.
You didn't specify whether the new table needs to be created as part of the command, so INSERT INTO ... SELECT FROM should be fine if your destination table already exists.

Pgpool install - libpq is not installed or libpq is old

Instead of compiling PgPool, why not just install it from package management? yum install postgresql-pgpool-II   I don't use CentOS, but that's it's package name in Fedora 17, and the two are usually consistent. Since you're using a PostgreSQL 9.2 installed via Yum on CentOS 5, you're obviously using a 3rd party packaging of PostgreSQL, so you're likely to be using the yum.postgresql.org repository. This repository claims to contain PgPool-II, so try: yum search pgpool   and see if you find any packages. Get details on them with: yum info packagename   Otherwise, for compiling PgPool you need the development package for PostgreSQL installed:   yum install postgresql-devel   If that alone doesn't do the trick, try making sure pg_config is on the PATH: export PATH=$PATH:/usr/pgsql-9.2/bin ./configure   You shouldn't need to specify a libdir or includedir explicitly, as the're in /usr/include and /usr/lib (or /usr/lib64) on CentOS and those are default search locations for configure and gcc. It's most likely that you just don't have the development headers installed.

How to create a backup of a single table in a postgres database?

C:\Program Files\PostgreSQL\9.0\bin\pg_dump.exe --host localhost --port 5432 --username postgres --format plain --ignore-version --verbose --file "C:\temp\filename.backup" --table public.tablename dbname

Use --table to tell pg_dump what table it has to backup. On Linux:

pg_dump -h localhost -p 5432 -U postgres -F c -i -v -f "/home/ssaret/table_name.backup" -t schema.table_name postgres

Extracting Information Schema Using Postgres Sql

Information schema provides information about tables, columns, views, trigger, functions and sequences in a database. We extract these information from simple SQL queries, lets we have a well maintained database and below are simple Sql statements to get various information in our database.

1/. List All Users

SELECT usename

FROM pg_user;

 

2/.List All Tables Name

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'BASE TABLE'

AND table_schema NOT IN

('pg_catalog', 'information_schema');

 

3/. List All Views

SELECT table_name

FROM information_schema.views

WHERE table_schema NOT IN ('pg_catalog', 'information_schema')

AND table_name !~ '^pg_';

 

4/. List All Table Name

SELECT  table_name FROM information_schema.tables

WHERE table_type = 'BASE TABLE' AND table_schema NOT IN

('pg_catalog', 'information_schema')

 

5/. List Name of the field, data type of table.

// Suppose emp is table name

 

SELECT column_name, data_type

FROM information_schema.columns

WHERE table_name = 'emp';

 

6/. List Table Indexes

// Suppose emp is table name

 

SELECT  relname FROM pg_class

WHERE oid IN

    (SELECT indexrelid FROM pg_index, pg_class

       WHERE pg_class.relname='emp'

        AND pg_class.oid=pg_index.indrelid

        AND indisunique != 't' AND indisprimary != 't')

 

7/. List Table Constraints

SELECT  constraint_name, constraint_type FROM information_schema.table_constraints

WHERE table_name = 'emp' AND constraint_type!='CHECK'

 

8/. List Triggers

SELECT DISTINCT trigger_name FROM information_schema.triggers

WHERE event_object_table = 'emp'

AND trigger_schema NOT IN ('pg_catalog', 'information_schema')

 

9/. List Functions

SELECT routine_name FROM information_schema.routines

WHERE specific_schema NOT IN ('pg_catalog', 'information_schema')

 

10/. List Sequences

SELECT relname FROM pg_class

WHERE relkind = 'S'

AND relnamespace IN

                   (SELECT oid FROM pg_namespace

                      WHERE nspname NOT LIKE 'pg_%'

                       AND nspname != 'information_schema')