PostgreSQL DDL Operations: Difference between revisions
Jump to navigation
Jump to search
Line 54: | Line 54: | ||
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<''db-name''>'; | SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<''db-name''>'; | ||
This will report a number of backends and it will take a while until all will be terminated. You may need to run it several times to insure that all backends are gone. When all backends are gone, the database can be dropped. | This will report a number of backends and it will take a while until all will be terminated. You may need to run it several times to insure that all backends are gone. | ||
If there is a lot of activity on the database, you may optionally disallow new connections: | |||
UPDATE pg_database SET datallowconn = 'false' WHERE datname = '<''db-name''>'; | |||
ALTER DATABASE <''db-name''> CONNECTION LIMIT 1; | |||
When all backends are gone, the database can be dropped. | |||
=Users= | =Users= |
Revision as of 19:07, 14 December 2018
Internal
Database Cluster
initdb
initdb will create a new database cluster.
Database
List Databases
psql -l [-h ... -U ... -d ...]
or
> \l
or
SELECT datname FROM pg_database;
Create a Database
createdb <dbname>
createdb -h localhost -U admin playground
or
CREATE DATABASE <dbname>;
Also see:
Connect to a Database
psql <database-name>
Drop a Database
DROP DATABASE <dbname>;
This command will not work if there are users connected to the database:
ERROR: database "..." is being accessed by other users DETAIL: There are 10 other sessions using the database.
The database can though be forcefully dropped as follows:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<db-name>';
This will report a number of backends and it will take a while until all will be terminated. You may need to run it several times to insure that all backends are gone.
If there is a lot of activity on the database, you may optionally disallow new connections:
UPDATE pg_database SET datallowconn = 'false' WHERE datname = '<db-name>'; ALTER DATABASE <db-name> CONNECTION LIMIT 1;
When all backends are gone, the database can be dropped.
Users
The following user operations are supported.
List Users
psql \du
or
SELECT usename FROM pg_user;
Create User
Command line:
createuser <user_name>
or SQL client:
CREATE USER <user_name>;
Change Password
In psql, for the current user:
\password <user_name>
or
ALTER USER user_name WITH PASSWORD 'new_password';
Granting Privileges on a Database
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <user>;
Tablespace
List Tablespaces
psql \db
Tables
List Tables
psql \dt
Create a Table
CREATE TABLE poc_library_component ( "id" integer NOT NULL, "name" text, "desc" text, CONSTRAINT poc_library_component_pk PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE poc_library_component OWNER TO is3_as;
Note: apparently, PostgreSQL does not like uppercases in column names.