PostgreSQL DDL Operations: Difference between revisions
Jump to navigation
Jump to search
Line 53: | Line 53: | ||
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. | |||
=Users= | =Users= |
Revision as of 19:05, 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. 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.