5862
Comment: Ident authentication error solving config added
|
6455
wording, use apt instead of aptitude
|
Deletions are marked like this. | Additions are marked like this. |
Line 2: | Line 2: |
||<tablestyle="width: 100%;" style="border: 0px hidden">~-[[DebianWiki/EditorGuide#translation|Translation(s)]]: [[PostgreSql|English]] - [[ru/PostgreSql|Русский]] -~||<style="text-align: right;border: 0px hidden"> (!) [[PostgreSql/Discussion|Discussion]]|| | ~-[[DebianWiki/EditorGuide#translation|Translation(s)]]: English - [[es/PostgreSql|español]] - [[it/PostgreSql|Italiano]] - [[ru/PostgreSql|Русский]]-~ |
Line 4: | Line 4: |
<<TableOfContents(2)>> | |
Line 6: | Line 5: |
== PostgreSQL Overview == | . [[WikiPedia:PostgresSQL|PostgreSQL]], also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. |
Line 8: | Line 9: |
---- | <<TableOfContents(2)>> |
Line 10: | Line 14: |
Required packages: postgresql postgresql-client | Required packages: [[DebPkg:postgresql]] [[DebPkg:postgresql-client]] |
Line 13: | Line 17: |
# aptitude install postgresql postgresql-client | # apt install postgresql postgresql-client |
Line 17: | 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 21: | 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 24: | Line 28: |
# 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 template1 }}} === New User and database === Create a regular system user account using '''adduser''' {{{ #adduser mypguser }}} Connect to database {{{ #su - postgres $psql template1 }}} Create new user, database and grant privileges: {{{ template1=#CREATE USER mypguser WITH PASSWORD 'mypguserpass'; template1=#CREATE DATABASE mypgdatabase; template1=#GRANT ALL PRIVILEGES ON DATABASE mypgdatabase to mypguser; }}} or {{{ #createuser mypguser -W #from regular shell #su - mypguser $psql template1 template1=#CREATE DATABASE mypgdatabase; |
# apt search postgresql |
Line 63: | Line 32: |
Quit from the database | == User access == Both the default database user and default database are called '''postgres'''. You will need administrative rights. As root: |
Line 65: | Line 40: |
template1=# \q | # su -c /usr/bin/psql postgres |
Line 68: | Line 43: |
Connect as user user to new database | If your system uses sudo to get administrative rights: |
Line 70: | Line 45: |
#su - mypguser psql mypgdatabase |
$ sudo -u postgres psql |
Line 74: | Line 48: |
or | === 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: |
Line 77: | Line 72: |
#psql -d mypgdatabase -U mypguser | $ psql -d mypgdatabase -h localhost -U mypguser |
Line 80: | Line 75: |
If you get errors like: | you can also use a ~/.pgpass file |
Line 82: | Line 77: |
Add line for auth : | |
Line 83: | Line 79: |
psql: FATAL: Ident authentication failed for user "salahuddin" | $ echo 'hostname:port:mypgdatabase:mypguser:mypgpassword' >> ~/.pgpass }}} Secure the file {{{ chmod 600 ~/.pgpass |
Line 86: | Line 86: |
edit '''pg_hba.conf''' in /etc/postgresql/8.4/main/pg_hba.conf |
Now you can easily connect with |
Line 89: | Line 88: |
local all all trust #replace ident or peer with trust | $ psql -d mypgdatabase -h localhost -U mypguser |
Line 92: | Line 91: |
restart postgresql {{{ # /etc/init.d/postgresql restart |
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 96: | Line 101: |
You can adapt and use : https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian |
|
Line 103: | 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 129: | 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 137: | 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 144: | Line 155: |
{{{#!wiki warning '''Warning!''' |
1. Run initdb with your options. For example: |
Line 147: | 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 162: |
---- | |
Line 153: | Line 163: |
See: | == See Also == |
Line 155: | Line 165: |
* [[DebianPkg:postgresql|PostgreSQL packages in Debian]] * [[DebianGis/UpdatingPostGIS]] (also describes upgrading postgresql) == External Links == * [[https://www.postgresql.org/|Official PostgreSQL web site]] |
|
Line 156: | Line 172: |
* WikiPedia:PostgreSQL | |
Line 158: | Line 173: |
* http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools * [[http://www.commandprompt.com/ppbook/|Practical PostgreSQL]] * http://packages.debian.org/unstable/misc/postgresql * [[GNOME-DB]] * [[UpdatingPostGIS]] (also describes upgrading 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/ * [[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, 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.
Contents
Installation
Required packages: postgresql postgresql-client
# apt 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). 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.
- Take root privileges.
- Run the following command:
pg_dropcluster --stop <version> main
For example:pg_dropcluster --stop 11 main
- 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
DebianGis/UpdatingPostGIS (also describes upgrading postgresql)
External Links
https://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
https://www.linode.com/docs/databases/postgresql/debian-6-squeeze/