From PostgreSQL wiki
please add info or links:
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;
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)); CREATE TABLE
Insert a single record into new table
postgres=>insert into test.test (coltest) values ('It works!'); INSERT 0 1
First SELECT from a table
postgres=>SELECT * from test.test; coltest ----------- It works! (1 row)
Drop test table
postgresql=>DROP TABLE test.test; DROP TABLE