Basic PostgreSQL administration

PostgreSQL is a capable DBMS that can serve a large variety of applications. PostgreSQL offers psql, which allows you to interact with it from a terminal and perform most tasks. If you execute psql without any parameters you will most likely get an error message similar to the following: $ psql psql: FATAL: database "mtsouk" does not exist

You can list all available PostgreSQL databases as follows, which is usually the first command you will need to execute: $ psql -l Then, you can specify the database you want to connect to as follows: $ psql -d postgres psql (9.5.3) Type "help" for help. postgres=# You can find the version of PostgreSQL you’re using by executing this command under psql: postgres=# SELECT version(); You can create a new PostgreSQL user called “LXF” as follows: postgres=# CREATE USER LXF WITH PASSWORD 'aPassword'; CREATE ROLE

Alternatively, you can use the createuser command line utility, which is provided by PostgreSQL, to create a new PostgreSQL user.

You can create a new database as follows: postgres=# CREATE DATABASE phoenix; CREATE DATABASE Alternatively, you can use the createdb command line utility, which is provided by PostgreSQL, to create a new database.

You can give full access to an existing user to an existing database as follows: postgres=# GRANT ALL PRIVILEGES ON DATABASE phoenix to LXF; GRANT postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON tables TO LXF; ALTER DEFAULT PRIVILEGES postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON sequences TO LXF; ALTER DEFAULT PRIVILEGES You can now connect to the Phoenix database as user “lxf” as follows: $ psql -d phoenix -U lxf -W -h 127.0.0.1 psql (9.5.3) Type "help" for help. phoenix=> With a little help from SQL, you can create a new table: phoenix=> CREATE TABLE test_table ( id bigserial primary key, name varchar(20) NOT NULL, comments text NOT NULL, date_added timestamp default NULL ); CREATE TABLE You can find more about the fields of an existing table as follows: phoenix=> \d+ test_table; You can delete the contents of an existing table without deleting the actual table as follows: postgres=# TRUNCATE table_name; You can delete an entire table including its contents as follows: phoenix=> DROP TABLE test_table; DROP TABLE

Finally, you can delete an entire database, including all its tables with all their contents, as follows: postgres=# DROP DATABASE phoenix; DROP DATABASE