Backing up one table with pg_dump (Windows)

 Navigate to the Postgres bin folder

cd "c:\Program Files\PostgreSQL\11\bin" 

Enter the pg_dump command

pg_dump -U postgres -W -F c -d ndlopfu -t transactions > c:\my_backups\ndlopfu_transactions.backup 

The options are as follows:

-U postgres: Use the user postgres

-W: Prompt for password

-F: Specifies the format. There are four options:

c: custom

d: directory 

t: tar

p: plain

-d ndlopfu: The database name

-t transactions: The table name

c:\my_backups\ndlopfu.backup: The backup path and filename 

Backing up a database with pg_dump (Windows)

Navigate to the Postgres bin folder

cd "c:\Program Files\PostgreSQL\11\bin" 

Enter the pg_dump command

pg_dump -U postgres -W -F c ndlopfu > c:\my_backups\ndlopfu.backup 

The options are as follows:

-U postgres: Use the user postgres

-W: Prompt for password

-F: Specifies the format. There are four options:

c: custom

d: directory 

t: tar

p: plain

ndlopfu: The database name

c:\my_backups\ndlopfu.backup: The backup path and filename 

 

 

 

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: