How to query a table in one PostgreSQL database from another PostgreSQL database using dblink

You can query a table from a different PostgreSQL database using the dblink extension. 

The steps are as follows:

1. Install the deblink extension
2. Create the server connection
3. Query the data
4. Disconnect the server

You can use SELECT  on the foreign table.

Please note the the server connection with dblink only lasts for the current session.

Install the dblink extension

The query to install the dblink extension is:

CREATE EXTENSION dblink;

Create the server connection

The query to create the server connection is:

SELECT dblink_connect('tloustore', 'host=127.0.0.1 port=5432 dbname=tloustore user=elephant password=secret'); 

 The arguments:

• The dblink name is tloustore
• host '127.0.0.1' is the ip address of the foreign server
• port '5432' is the port of the foreign server
• dbname=tloustore is the database on the foreign in the foreign server
• user=elepahnt is the username for the foreign server
• password=secret is the password for the foreign server

Query the data

Do the following select query to query the foreign data:

SELECT * FROM dblink('tloustore',

'SELECT itmcode, description

FROM inventory;')

AS tlou_inventory(itmcode integer, description character varying);

The arguments:

• tloustore is the dblink connection name
• 'SELECT itmcode, description FROM inventory;' is the query on the foreign table
• AS tlou_inventory(itmcode integer, description character varying); is the alias on the local database

Disconnect the server

The query to disconnect the dblink server is:

SELECT dblink_disconnect('tloustore');

Example

How to query a table in one PostgreSQL database from another PostgreSQL database using a foreign data wrapper

You can query a table from a different PostgreSQL database using the PostgreSQL Foreign data wrapper (postgres_fdw).

The steps are as follows:

1. Install the postgres_fdw extension
2. Create the foreign server connection
3. Create a user mapping
4. Create a foreign table

You can use SELECT, UPDATE, INSERT and DELETE on the foreign table.

Install the postgres_fdw extension

The query to install the foreign data wrapper extension is:

CREATE EXTENSION postgres_fdw;

 Create the foreign server connection

The query to create the foreign server connection is:

CREATE SERVER tloustore_srv   

FOREIGN DATA WRAPPER postgres_fdw   

OPTIONS (host '127.0.0.1', dbname 'tloustore', port '5432');  

The arguments:

• tloustore_srv is the name I am giving to the foreign server
• host '127.0.0.1' is the ip address of the foreign server
• dbname 'tloustore' is the database on the foreign in the foreign server
• port '5432' is the port of the foreign server

Create the user mapping

The query to create the user mapping is:

CREATE USER MAPPING FOR admin 

SERVER tloustore_srv   

OPTIONS (user 'elephant', password 'secret'); 

 The arguments:

• admin is the user on the database I am connected to
• tloustore_srv is the foreign server we created
• user 'elephant' is the user on the foreign database
• password 'secret' is the password on the foreign database

Create a foreign table

You need to create a foreign table in you current database before you can query the table. You can add all the table columns or only a few. The query is as follows:

CREATE FOREIGN TABLE foreign_inventory 

( 

    itmcode integer NOT NULL, 

    description character varying, 

    price numeric(15,2)

) 

SERVER tloustore_srv  

OPTIONS (schema_name 'public', table_name 'inventory'); 

Query the data

You can now query the foreign table just like any local table. 

SELECT * FROM foreign_inventory;

Example: 

Notes:

1) primary key constraints are not supported on foreign tables

2) --If you don't want to import individual tables one-by-one, then import the schema instead using IMPORT FOREIGN SCHEMA:

IMPORT FOREIGN SCHEMA production  

LIMIT TO (tracks, pages)  

FROM SERVER segment INTO public; 

Locate record in one table with no matching record in another

There are two options to locate a record in one table with no related record in another. In this example I have a customer table containing customers and an invoice table containing invoices.

The following query will display all the record in the customer table with no matching records in the invoice table. It is easy to remember, but the execution of the query is slow. 

The next query will give the same result, but it will execute a lot faster because it will use a hash index.

Create users or roles in PostgreSQL

Introduction

PostgreSQL uses roles to manage access and permissions to databases. A role can be either a user or a group depending on how you set up the role.

The difference between a user and a group is that a user has LOGIN privilege and a group does not.

Groups are mainly used to set privileges for a group of users. If you create a group with certain privileges you can add them to new users. If you change the group's privileges, the privileges for all the users in the group will change as well.

Create Role

Use the following query to create a role:

This will create a role called thomas without LOGIN privilege.

Use the any of the following queries to create a role with LOGIN privilege:

Both will create a role (user) called thomas with LOGIN privilege.

Parameters

CREATE ROLE will accept the following parameters. (The second parameter in the list is the default)

1. SUPERUSER or NONSUPERUSER
2. CREATEDEB or NOCREATEDB
3. CREATEROLE or NOCREATEROLE
4. NOINHERIT or INHERIT
5. LOGIN or NOLOGIN
6. REPLICATION or NOREPLICATION
7. CONNECTION LIMIT connlimit - The default is -1 which means unlimited concurrent connections can be made with this role
8. PASSWORD password
9. VALID UNTIL 'timestamp'

Grant and Revoke

Additional privileges can be granted and revoked to and from roles. These include the following:

1. SELECT
2. INSERT
3. DELETE
4. UPDATE
5. TRUNCATE
6. REFERENCES - to create foreign key contraints
7. TRIGGER
8. CREATE - to create new schemas or objects in schemas
9. CONNECT - allows the role to connect to a specified database
10. ALL PRIVILEGES - to grant all privileges

Inheritance

A role can inherit privileges from another role.

Create a role and add some privileges:

Create a second role and grant the first role to the second role:

If you connect to PostgreSQL as bob you will have all the privileged granted to john.

List of available users in a cluster

A list of all available roles in a cluster can be queried from the pg_roles catalog.

How to get a list of all the databases in PostgreSQL

You can get a list of all the databases in a PostgreSQL cluster by either using a psql meta command or by executing a query.

PSQL

1. Open psql
2. Type \l (lowercase L) and press Enter

Query

The query to view a list of all databases in a cluster is:

How get a list of all the tables in a PostgreSQL database

You can get a list of all the tables in a PostgreSQL database with the built in psql functions or by executing a query:

PSQL

1. Open psql and log into the database for which you want the list of tables
2. Type \dt and press Enter

Query

You can also query pg_tables to get a list of tables in a database. You can filter on the schema name in the where clause:

How much disk space does a PostgreSQL database, table or index use?

The amount of disk space used by a PostgreSQL database, table or index can be obtained by querying the PostgreSQL data dictionary.

Database size

The query

will return the current database size in bytes.

To view the size in K, MB or GB you can run the query:

Table of Index size

Tables and Indexes are referred to as relations in PostgreSQL. Run the following query to view the size of a relation in bytes:

To view the size in K, MB or GB you can run the query:

How to restore a backup made plain format

PGAdminIII can make backups in three formats:

1. Plain
2. Custom
3, Tar

Custom and Tar backups can be restored with PGAdminIII or pg_restore. Plain backups must be restored with psql.

Option 1:

1. Open psql and log into the database postgres
2. Type CREATE DATABASE databasename;

3. Log into the database by typing \c databasename. (in this example \c test)

4. Type \i path/to/backupfile (in this example \i c:/pgbackup/testplain.backup)

5. The backup will now be restored

Option 2

1. Go to the command prompt (Windows) or the console (Linux) and make sure that the user is the postgres user

2. On Windows: Press Shift and Right Click on the Command Prompt icon and select Run as different user

3. On Linux: Type su postgres to change the user to the postgres user

4. Go to the bin directory (it is a sub directory under PostgreSQL/10)

5. Type createdb databasename (in this example createdb test). This will create the database

6. Type psql databasename < path/to/backupfile (in this example psql test < c:/pgbackup/testplain.backup)

7. The backup will now be restored

Adding a primary key to a table

A properly designed table (in a relational database) will always have a primary key. This is essential to uniquely identify records and to relate to other tables.

Unfortunately there are a lot of databases with tables without a primary key. The good news is that it is very easy to correct. You can either add a new column with a primary key constraint or you can add a primary key constraint to an existing column.

Add a column called id to the table and set it as the primary key

Let's say we have an employee table like this:

We are going to add a column named id and set it as a primary key. The id column will be numbered automatically. Please note that the SQL syntax for Microsoft SQL Server differs from the syntax for MySQL and PostgreSQL. The queries are as follows:

Microsoft SQL Server 2008:

MySQL & PostgreSQL:

The amended employee table will look like this:

Add a primary key constraint to an existing column

A primary constraint can only be added to a column if:

1.            There are only unique values in the column; and

2.            The column is not able to contain NULL values.

If your table looked like this (but id were not a primary key) 

you can execute the following query to add a primary key constraint:

How to locate the files in which a table or index (relation) is stored in PostgreSQL

PostgreSQL refers to tables and indexes as relations. In every database you will find information (meta data) about relations in the pg_class table.

Relations are stored in files in a directory for each database in the base directory. (If you don't know how to locate the base directory or the database's directory you can refer to my blog entries How to locate the data or cluster directory in PostgreSQL and How to locate the directory that contains a database in PostgreSQL)

Each file on the hard drive will refer to only one relation. The maximum file size of a file is 1GB. If a relation is larger than 1GB he relation will be stored in more than one file. The relation's file name is stored in the relfilenode field in the pg_class table. The relations's name itself is stored in the relname field of the pg_class table.

The query to locate the relation's file name is as follows:

The table employee is therefore stored in a files called 433981 on the hard drive. The file will be located in the database's directory in the data directory.

If the table is larger than 1 GB the table will be stored in multiple files like 24765, 24765.1 and 24765.2.

How to locate the directory that contains a database in PostgreSQL

First of all you need to locate the data directory in which the databases are stored. If you do not know where to find the data directory you can read my blog entry: How to locate the data or cluster directory in PostgreSQL

In the data directory you will find a base directory and in the base directory there is a single directory per database. A database's directory name will always be a number.

The query

will display the Object ID and a database name for every database in the cluster.

Every database is stored in a single directory in the .../data/base directory. The database's directory name is the same as the databases's Object ID.

For example: The postgres database is stored in the 12603 directory.

How to log all the queries in PostgreSQL

The logging settings for PostgreSQL are contained in the postgresql.conf file in the data folder. (It is always a good idea to make a backup of the postgresql.conf file before you make any changes to it.)

Open the postgresql.conf file with a text editor. The logging options are after the following lines:

#------------------------------------------------------------------------------

# ERROR REPORTING AND LOGGING

#------------------------------------------------------------------------------

Change:

#log_min_duration_statement = -1

to:

log_min_duration_statement = 0

and change

#log_statement = 'none'

to:

log_statement = 'all'

Make sure that you remove the # before the changed lines.

Save the amended postgresql.conf file and restart the PostgreSQL service. Alternatively you can reload the postgresql.com file while the server is running with pg_ctl reload.

Change a column name in PostgreSQL, MySql and SQL Server

I am often asked how to rename a column in an existing (SQL) table.

First of all I want to warn that one should be careful when changing column names in existing tables as the changes can affect scripts, functions and procedures written for the database.

The queries to change column names are fairly easy to understand, but the syntax used for SQL Server, MySQL and PostgreSQL differs from each other..

The basic queries for the three database systems are as follows:

PostgreSQL

MySQL

SQL Server

Example:

The queries will be as follows:

PostgreSQL

MySQL

SQL Server

The result: