Differences between revisions 39 and 61 (spanning 22 versions)
Revision 39 as of 2013-09-25 16:41:41
Size: 6078
Editor: GeoffSimmons
Comment: Define "See Also" and "External Link" sections, add links, update header from DefaultTemplate.
Revision 61 as of 2020-12-07 17:35:30
Size: 6455
Editor: nodiscc
Comment: wording, use apt instead of aptitude
Deletions are marked like this. Additions are marked like this.
Line 2: Line 2:
~-[[DebianWiki/EditorGuide#translation|Translation(s)]]: English - [[it/PostgreSql|Italiano]] - [[ru/PostgreSql|Русский]]-~ ~-[[DebianWiki/EditorGuide#translation|Translation(s)]]: English - [[es/PostgreSql|español]] - [[it/PostgreSql|Italiano]] - [[ru/PostgreSql|Русский]]-~
Line 4: Line 4:

 . [[WikiPedia:PostgresSQL|PostgreSQL]], also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.


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.
Line 7: Line 12:
== 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.
Line 11: Line 14:
Required packages: postgresql postgresql-client Required packages: [[DebPkg:postgresql]] [[DebPkg:postgresql-client]]
Line 14: Line 17:
# apt-get install postgresql postgresql-client # apt install postgresql postgresql-client
Line 18: Line 21:
 * postgresql-doc - PostgreSQL documentation.
 * pgadmin3 - PostgreSQL administration GUI.
 * phppgadmin - PostgreSQL web-based administration tool.
 * [[DebPkg:postgresql-doc]] - PostgreSQL documentation.
 * [[DebPkg:pgadmin3]] - PostgreSQL administration GUI.
 * [[DebPkg:phppgadmin]] - PostgreSQL web-based administration tool.
Line 22: Line 25:
Please note that the procedural languages are installed separately (plpgsql comes by default). Perform search packaging database to find the list of possibilities: Please note that the procedural languages are installed separately (plpgsql comes by default). Search Debian packages to find the list of possibilities:
Line 25: Line 28:
# aptitude search postgresql # apt search postgresql
Line 28: Line 31:
Line 29: Line 33:
Line 31: Line 36:
You may connect to the database using the following command: You will need administrative rights.

As root:
Line 33: Line 40:
# su - postgres
$ psql
# su -c /usr/bin/psql postgres
Line 36: Line 42:

If your system uses sudo to get administrative rights:
{{{
$ sudo -u postgres psql
}}}
Line 43: Line 55:
Connect to database Connect to database and create a new database user and a database:
Line 46: Line 58:
$ psql $ createuser --pwprompt mypguser #from regular shell
$ createdb -O mypguser mypgdatabase
Line 49: Line 62:
Create a new database user and a database:
{{{
postgres=# CREATE USER mypguser WITH PASSWORD 'mypguserpass';
postgres=# CREATE DATABASE mypgdatabase OWNER mypguser;
}}}
or
{{{
# createuser mypguser #from regular shell
# createdb -O mypguser mypgdatabase
}}}

Quit from the database
{{{
postgres=# \q
}}}
Line 71: Line 69:
or or, if the OS user name is not the same as the database user name:
Line 74: Line 72:
# psql -d mypgdatabase -U mypguser $ psql -d mypgdatabase -h localhost -U mypguser
Line 77: Line 75:
If you get errors like: you can also use a ~/.pgpass file

Add line for auth :
Line 79: Line 79:
psql: FATAL: Ident authentication failed for user "mypguser" $ echo 'hostname:port:mypgdatabase:mypguser:mypgpassword' >> ~/.pgpass
}}}
Secure the file
{{{
chmod 600 ~/.pgpass
Line 82: Line 86:
edit '''pg_hba.conf''' in /etc/postgresql/X.Y/main/pg_hba.conf
Now you can easily connect with
Line 85: Line 88:
local all all trust # replace ident or peer with trust $ psql -d mypgdatabase -h localhost -U mypguser
Line 88: Line 91:
reload postgresql
{{{
# /etc/init.d/postgresql reload
More info on the syntax can be found here : https://www.postgresql.org/docs/11/libpq-pgpass.html

== Migration ==
{{{#!wiki warning
'''Warning!'''

Warning this part need to be validate !!
If i remember well there is a better debian way.
Line 92: Line 101:
You can adapt and use :

https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian
Line 99: Line 111:
PostgreSQL documentation points to tutorial, which is included in postgresql-doc package. To get more information look at PostgreSQL documentation points to tutorial, which is included in the DebianPkg:postgresql-doc package. Once the package is installed, to get more information look at
Line 125: Line 137:
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: Debian PostgreSQL installation automatically calls initdb i.e. it initializes the cluster with default encoding and locale. To change the locale (and possibly other options in initdb), delete the existing default cluster and create a new one:

{{{#!wiki warning
'''Warning!'''

The following operation obviously deletes everything you had in cluster databases. Perform this operation right after you have installed the base package.
}}}
Line 133: Line 152:
pg_dropcluster --stop 8.3 main
 }}}
 1. Run the initdb with your options. For example:
 {{{
pg_createcluster --locale de_DE.UTF-8 --start 8.3 main
pg_dropcluster --stop 11 main
Line 140: Line 155:
{{{#!wiki warning
'''Warning!'''
 1. Run initdb with your options. For example:
Line 143: Line 157:
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).
}}}
 {{{
pg_createcluster --locale de_DE.UTF-8 --start 11 main
 }}}
Line 151: Line 166:
 * [[https://lists.alioth.debian.org/mailman/listinfo/pkg-postgresql-public|PostgreSQL on Debian email list]]
 * [[GNOME-DB]]
Line 157: Line 170:
 * [[http://www.postgresql.org/|Official PostgreSQL web site]]
 * [[WikiPedia:PostgreSQL|PostgreSQL entry in Wikipedia]]
 * [[https://www.postgresql.org/|Official PostgreSQL web site]]
Line 161: Line 173:
 * http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
 * [[http://www.commandprompt.com/ppbook/|Practical PostgreSQL]]
 * http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
 * http://library.linode.com/databases/postgresql/debian-6-squeeze
 * https://wiki.postgresql.org/wiki/Design_Tools
 * [[https://web.archive.org/web/20011216220127/http://www.commandprompt.com/ppbook/|Practical PostgreSQL]]
 * https://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
 * https://www.linode.com/docs/databases/postgresql/debian-6-squeeze/
Line 166: Line 178:
 * http://www.debianhelp.co.uk/postgresql.htm
 * http://www.debianhelp.co.uk/postgresqlweb.htm

-----------

CategoryNetwork CategorySoftware CategoryDatabase

Translation(s): English - español - Italiano - Русский


  • PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.

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 install postgresql postgresql-client

Recommended packages:

Please note that the procedural languages are installed separately (plpgsql comes by default). Search Debian packages to find the list of possibilities:

# apt search postgresql

User access

Both the default database user and default database are called postgres.

You will need administrative rights.

As root:

# su -c /usr/bin/psql postgres

If your system uses sudo to get administrative rights:

$ sudo -u postgres psql

New User and database

Create a regular system user account using adduser (skip this step to use an existing account):

# adduser mypguser    #from regular shell

Connect to database and create a new database user and a database:

# su - postgres
$ createuser --pwprompt mypguser    #from regular shell
$ createdb -O mypguser mypgdatabase

Connect as user mypguser to new database

# su - mypguser
$ psql mypgdatabase

or, if the OS user name is not the same as the database user name:

$ psql -d mypgdatabase -h localhost -U mypguser

you can also use a ~/.pgpass file

Add line for auth :

$ echo 'hostname:port:mypgdatabase:mypguser:mypgpassword' >> ~/.pgpass

Secure the file

chmod 600 ~/.pgpass

Now you can easily connect with

$ psql -d mypgdatabase -h localhost -U mypguser

More info on the syntax can be found here : https://www.postgresql.org/docs/11/libpq-pgpass.html

Migration

Warning!

Warning this part need to be validate !! If i remember well there is a better debian way.

You can adapt and use :

https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian

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 the postgresql-doc package. Once the package is installed, 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 initdb i.e. it initializes the cluster with default encoding and locale. To change the locale (and possibly other options in initdb), delete the existing default cluster and create a new one:

Warning!

The following operation obviously deletes everything you had in cluster databases. Perform this operation right after you have installed the base package.

  1. Take root privileges.
  2. Run the following command:
    pg_dropcluster --stop <version> main
    For example:
    pg_dropcluster --stop 11 main
  3. Run initdb with your options. For example:
    pg_createcluster --locale de_DE.UTF-8 --start 11 main

Check the Locale page to see how to add more locales.

See Also


CategoryNetwork CategorySoftware CategoryDatabase