5839
Comment: trying bring back the padding
|
5825
improve whitespace, don't use template database for work
|
Deletions are marked like this. | Additions are marked like this. |
Line 4: | Line 4: |
<<TableOfContents(2)>> | <<TableOfContents(2)>> |
Line 13: | Line 14: |
# aptitude install postgresql postgresql-client | # apt-get install postgresql postgresql-client |
Line 32: | Line 33: |
#su - postgres $psql template1 |
# su - postgres $ psql |
Line 39: | Line 40: |
#adduser mypguser | # adduser mypguser |
Line 44: | Line 45: |
#su - postgres $psql template1 |
# su - postgres $ psql |
Line 50: | Line 51: |
template1=#CREATE USER mypguser WITH PASSWORD 'mypguserpass'; template1=#CREATE DATABASE mypgdatabase; template1=#GRANT ALL PRIVILEGES ON DATABASE mypgdatabase to mypguser; |
postgres=# CREATE USER mypguser WITH PASSWORD 'mypguserpass'; postgres=# CREATE DATABASE mypgdatabase; postgres=# GRANT ALL PRIVILEGES ON DATABASE mypgdatabase to mypguser; |
Line 56: | Line 57: |
#createuser mypguser -W #from regular shell #su - mypguser $psql template1 template1=#CREATE DATABASE mypgdatabase; |
# createuser mypguser #from regular shell # su - mypguser $ psql postgres postgres=# CREATE DATABASE mypgdatabase; |
Line 65: | Line 66: |
template1=# \q | postgres=# \q |
Line 70: | Line 71: |
#su - mypguser psql mypgdatabase |
# su - mypguser $ psql mypgdatabase |
Line 77: | Line 78: |
#psql -d mypgdatabase -U mypguser | # psql -d mypgdatabase -U mypguser |
Line 85: | Line 86: |
edit '''pg_hba.conf''' in /etc/postgresql/8.4/main/pg_hba.conf | edit '''pg_hba.conf''' in /etc/postgresql/X.Y/main/pg_hba.conf |
Line 88: | Line 89: |
local all all trust #replace ident or peer with trust | local all all trust # replace ident or peer with trust |
Line 91: | Line 92: |
restart postgresql | reload postgresql |
Line 93: | Line 94: |
# /etc/init.d/postgresql restart | # /etc/init.d/postgresql reload |
Translation(s): English - Italiano - Русский |
?Discussion |
Contents
PostgreSQL Overview
PostgreSQL has extensive and good help that should be the first source of information regarding this database product. This page outlines main differences to generic PostgreSQL installation used by Debian.
Installation
Required packages: postgresql postgresql-client
# apt-get install postgresql postgresql-client
Recommended packages:
- postgresql-doc - PostgreSQL documentation.
- pgadmin3 - PostgreSQL administration GUI.
- phppgadmin - PostgreSQL web-based administration tool.
Please note that the procedural languages are installed separately (plpgsql comes by default). Perform search packaging database to find the list of possibilities:
# aptitude search postgresql
User access
The default user is postgres and default database template1.
you may connect to database using the following command.
# su - postgres $ psql
New User and database
Create a regular system user account using adduser
# adduser mypguser
Connect to database
# su - postgres $ psql
Create new user, database and grant privileges:
postgres=# CREATE USER mypguser WITH PASSWORD 'mypguserpass'; postgres=# CREATE DATABASE mypgdatabase; postgres=# GRANT ALL PRIVILEGES ON DATABASE mypgdatabase to mypguser;
or
# createuser mypguser #from regular shell # su - mypguser $ psql postgres postgres=# CREATE DATABASE mypgdatabase;
Quit from the database
postgres=# \q
Connect as user mypguser to new database
# su - mypguser $ psql mypgdatabase
or
# psql -d mypgdatabase -U mypguser
If you get errors like:
psql: FATAL: Ident authentication failed for user "mypguser"
edit pg_hba.conf in /etc/postgresql/X.Y/main/pg_hba.conf
local all all trust # replace ident or peer with trust
reload postgresql
# /etc/init.d/postgresql reload
Documentation
To get an overview about Debian's PostgreSQL architecture, instructions for a quick start, and pointers to the programs and manpages, have a look at /usr/share/doc/postgresql-common/README.Debian.gz.
Tutorial files
PostgreSQL documentation points to tutorial, which is included in postgresql-doc package. To get more information look at /usr/share/doc/postgresql-doc-[version]/tutorial/README.
Listing existing database clusters
Use pg_lsclusters command to check installed clusters and obtain some basic information such as: version (major version), name, port, status (online or down), owner, data directory and log file.
pg_lsclusters
pg_ctl replacement
pg_ctl is a PostgreSQL command line control program that can be used to control the database. Debian has made a Perl-wrapper for the pg_ctl called /usr/bin/pg_ctlcluster. Use the pg_ctlcluster whenever you need the pg_ctl. To customize the behavior check the /etc/postgresql/[version]/[cluster]/pg_ctl.conf
Debian installs SysV-init compatible (standard) start-up script /etc/init.d/postgresql-[version]. It can be used to start, stop, restart and reload the system. It calls pg_ctlcluster internally.
File locations
Debian splits the database configuration from the database files, opposed to generic PostgreSQL installation that puts everything under same directory. Note that Debian allows multiple clusters and even different versions of PostgreSQL to co-exist in same host.
Configuration files: /etc/postgresql/[version]/[cluster]/
Binaries: /usr/lib/postgresql/[version]
Data files: /var/lib/postgresql/[version]/[cluster]
Log files: Installing PostgreSQL creates log directory /var/log/postgresql/. Starting the database engine creates log file with name postgresql-[version]-[cluster].log.
Changing Debian default installation
Debian PostgreSQL installation automatically calls the initdb i.e. it initializes the cluster with default encoding and locale. Encoding can be changed later but the locale cannot. To change the locale (an possibly other options in initdb), delete the existing default cluster and create a new one:
- Take root privileges.
- Run the following command:
pg_dropcluster --stop <version> main
For example:pg_dropcluster --stop 8.3 main
- Run the initdb with your options. For example:
pg_createcluster --locale de_DE.UTF-8 --start 8.3 main
Warning!
The previous operation obviously deletes everything you had in cluster databases. Perform this operation right after you have installed the base package. Check the PostgreSQL manual if you need to change locale for an existing database (it is not a trivial operation).
Check the Locale page to see how to add more locales.
See: