First steps

From PostgreSQL wiki
Jump to navigationJump to search


please add info or links:

install postgres

Follow steps for your OS.


Typically installing postgres to your OS creates an "initial DB" and starts the postgres server daemon running. If not then you'll need to run initdb


 # typically initdb creates a table named "postgres" owned by user "current logged in user name"
 # however, when you use pg_ctl it defaults to connecting as user "current user" to database "current user" so specify user postgres here 
 # so that it avoids confusion of having them named differently.
 $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -U postgres

and start the postgres daemon through some means, then proceed ref.

First steps after installation

First connect/login as root

 # su - postgres
 $ psql
 psql (9.6.0)
 Type "help" for help.
 # or in windows, current user doesn't matter
 c:\path\to\psql.exe -U postgres
 psql (9.6.0)
 Type "help" for help.
 postgres=#  help
 You are using psql, the command-line interface to PostgreSQL.
 Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit

Create a schema called test in the default database called postgres

 postgres=# CREATE SCHEMA test;

Create a role (user) with password

 postgres=# CREATE USER xxx PASSWORD 'yyy';

Grant privileges (like the ability to create tables) on new schema to new role

 postgres=# GRANT ALL ON SCHEMA test TO xxx;

Grant privileges (like the ability to insert) to tables in the new schema to the new role

 postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;


 postgres=# \q

Became a standard user.
The default authentication mode is set to 'ident' which means a given Linux user xxx can only connect as the postgres user xxx.

 # su - xxx

Login from xxx user in shell to default postgres db

 xxx$ psql -d postgres
 psql (9.2.4)
 Type "help" for help.
 # in windows
 # c:\path\to\psql.exe -U xxx -d postgres # again, windows doesn't care what your current user is, by default
 psql (9.6.0)
 Type "help" for help.

Create a table test in schema test

 postgres=>CREATE TABLE test.test (coltest varchar(20));

Insert a single record into new table

 postgres=>insert into test.test (coltest) values ('It works!');

First SELECT from a table

 postgres=>SELECT * from test.test;
  It works!
 (1 row)

Drop test table

 postgresql=>DROP TABLE test.test;