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 postgresqlNow, the debian package postgresql installs all dependencies and the client.
libpq5 postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-commonAlternatively, 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 trustBut 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