To start a shell connection:

psql -h hostname -U username databasename

Import data into a table:

psql -h hostname -U username database_name > data.sql 

The database can return its own configuration file location on the server with:

SHOW config_file;

Same for data directory:

SHOW data_directory;

OR:

select setting from pg_settings where name = 'Name of a setting';

CREATING DATABASES, CHANGING SCHEMAS and the normal tasks:

Use schema name with period in psql command to obtain information about this schema.

Setup:

test=# create schema test_schema;
CREATE SCHEMA
test=# create table test_schema.test_table (id int);
CREATE TABLE
test=# create table test_schema.test_table_2 (id int);
CREATE TABLE

Show list of relations in test_schema:

test=# \dt test_schema.
               List of relations
   Schema    |     Name     | Type  |  Owner   
-------------+--------------+-------+----------
 test_schema | test_table   | table | postgres
 test_schema | test_table_2 | table | postgres
(2 rows)

Show test_schema.test_table definition:

test=# \d test_schema.test_table
Table "test_schema.test_table"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

Show all tables in test_schema:

test=# \d test_schema.
Table "test_schema.test_table"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

Table "test_schema.test_table_2"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

Example from my question on stackoverflow

Quering database for active connections:

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND procpid <> pg_backend_pid();

PostgreSQL 9.2 and above:

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

Database dump:

More on handling large database backups is found on postgres documentation in section 24.1.3

For password less login:

sudo -u user_name psql db_name

To reset the password if you have forgotten:

ALTER USER "user_name" WITH PASSWORD 'new_password';

Leave a comment