Differences between revisions 1 and 56 (spanning 55 versions)
Revision 1 as of 2005-01-11 18:02:52
Size: 82
Editor: anonymous
Comment:
Revision 56 as of 2020-01-03 03:46:23
Size: 6365
Editor: leela52452
Comment: alioth is retired, hence removed PostgreSQL on Debian email list
Deletions are marked like this. Additions are marked like this.
Line 1: Line 1:
## Auto-converted by kwiki2moinmoin v2005-10-07
See:
#language en
~-[[DebianWiki/EditorGuide#translation|Translation(s)]]: English - [[es/PostgreSql|español]] - [[it/PostgreSql|Italiano]] - [[ru/PostgreSql|Русский]]-~
----
Line 4: Line 5:
 * WikiPedia:["PostgreSQL"] <<TableOfContents(2)>>

== 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 ==
Both the default database user and default database are called '''postgres'''.

You will need administrative rights.

As root:
{{{
# su -s /bin/bash postgres
}}}

If your system uses sudo to get administrative rights:
{{{
$ sudo -u postgres bash
}}}

Then, just start the client:
{{{
$ 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 ==
{{{#!wiki warning
'''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 postgresql-doc package. To get more information look at
`/usr/share/doc/postgresql-doc-[version]/tutorial/README`.

Need the postgresql-doc-[version] package.

== 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]/<<BR>>
Binaries: /usr/lib/postgresql/[version]<<BR>>
Data files: /var/lib/postgresql/[version]/[cluster]<<BR>>

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:

{{{#!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.
}}}

 1. Take root privileges.
 1. Run the following command:
 {{{
pg_dropcluster --stop <version> main
 }}}
 For example:
 {{{
pg_dropcluster --stop 11 main
 }}}

 1. 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 ==

 * [[DebianPkg:postgresql|PostgreSQL packages in Debian]]
 * [[DebianGis/UpdatingPostGIS]] (also describes upgrading postgresql)

== External Links ==

 * [[https://www.postgresql.org/|Official PostgreSQL web site]]
 * [[WikiPedia:PostgreSQL|PostgreSQL entry in Wikipedia]]
 * [[http://www.debianhelp.co.uk/postgresql.htm|PostgreSQL in Debian Help web site]]
 * http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools
 * 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/
 * [[http://petereisentraut.blogspot.com/2011/06/enabling-core-files-for-postgresql-on.html|Enabling core files for PostgreSQL on Debian]]
 * http://www.debianhelp.co.uk/postgresql.htm
 * http://www.debianhelp.co.uk/postgresqlweb.htm

-----------

CategoryNetwork CategorySoftware CategoryDatabase

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


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

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

You will need administrative rights.

As root:

# su -s /bin/bash postgres

If your system uses sudo to get administrative rights:

$ sudo -u postgres bash

Then, just start the client:

$ 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 postgresql-doc package. To get more information look at /usr/share/doc/postgresql-doc-[version]/tutorial/README.

Need the postgresql-doc-[version] package.

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