PostgreSQL Notes

PostgreSQL Debian Notes.

Install PostgreSQL on debian
Login, login using psql, list databases and users , set postgres password.
Authentication Modes and Access.
Create a new user and a new database.
Delete database
Vacuum
Enable remote Access
Logical backup with pg_dump




Install PostgreSQL from debian packages. 
# apt-get install postgresql
Now, the debian package postgresql installs all dependencies and the client. 
libpq5 postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common
Alternatively, you may install PostgreSQL from apt repositories maintained by the PostgreSQL Global Development Group.



Login as the postgres user using psql--the PostgreSQL interactive terminal. 
# su - postgres -c psql 
psql (9.1.14)
Type "help" for help.

postgres=#


The default authentication mode is 'ident' or 'peer' i.e. system user x can only login as PostgreSQL user x.

The PostgreSQL Client Authentication Configuration File in debian is at 
/etc/postgresql/*/main/pg_hba.conf


The 'trust' authentication mode allows connections unconditionally and the mode 'password' requires the client to supply an unencrypted password.

Since the default local authentication mode is 'peer', applications that make use of the database locally may give authentication errors if running as another system user. You may want to check if setting the mode to 'trust' fixes the problem. 
# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     trust
But not settle for 'trust' i.e. allow local connections unconditionally.

By default PostgreSQL binds to 127.0.0.1:5432 --you can change it at 
/etc/postgresql/*/main/postgresql.conf
'listen_addresses' if you need to enable remote access.

List databases. 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)
Most new databases are created by copying tempate1 of the templates.

List users. 
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}


Set the password for the PostgreSQL user postgres. 
postgres=# \password postgres
Enter new password: 
Enter it again:


Exit psql. 
postgres-# \q
#


Create a new PostgreSQL user. 
# su - postgres -c "createuser -P puser"
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n


Create the database puser that belongs to user puser. 
# su - postgres -c "createdb -O puser puser"


List PostgreSQL databases and user roles. 
# su - postgres -c psql
psql (9.1.14)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 puser     | puser    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 puser     |                                                | {}

postgres=# \q
#


Delete the database puser. 
# su - postgres -c "dropdb puser"


Calculate statistics for use by the optimizer(-z) and Garbage-Collect i.e. full vacuum(-f) all(-a) PostgreSQL databases in this host. 
# su - postgres -c "vacuumdb -a -f -z"
Vacuumdb is a VACUUM wrapper (pg_wrapper) written in Perl.



pg_dump - Logical Backups

Pg_dump extracts a PostgreSQL database into a script file or other archive file.

Create a compressed sql script file with the schema and data of the database puser that belongs to the PostgreSQL user puser in host a. 
a# pg_dump -o -U puser -h localhost puser |gzip > puser.dump.gz
Password:


Restore PostgreSQL database in host b. 
b# gunzip  puser.dump.gz
b# su - postgres -c "createuser -P puser"
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
b# su - postgres -c "createdb -O puer puser"
b# psql -U puser -h localhost < puser.dump 
Password for user puser:




PostgreSQL Notes



Labels: , , ,