12/05/2015 By emehany
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';
Login to Comment