特殊:Badtitle/NS100:PostgreSQL:修订间差异
小无编辑摘要 |
小无编辑摘要 |
||
第16行: | 第16行: | ||
You may also use the Synaptic package manager from the System>Administration menu to install these packages. | You may also use the Synaptic package manager from the System>Administration menu to install these packages. | ||
==== Gutsy ==== | ==== Gutsy ==== | ||
To install '''Postgresql-8.2''' using your favorite package manager install the '''postgresql''' package. | To install '''Postgresql-8.2''' using your favorite package manager install the '''postgresql''' package. | ||
==== Hardy ==== | |||
Install the latest Postgresql (v8.3) from the command line with the following command: | |||
<pre><nowiki> | |||
sudo apt-get install postgresql | |||
</nowiki></pre> | |||
Or you can use synaptic/adept to install the postgresql package. | |||
=== Basic Server Setup === | === Basic Server Setup === | ||
To start off, we need to change the [[UbuntuHelp:PostgreSQL|PostgreSQL]] postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command. | To start off, we need to change the [[UbuntuHelp:PostgreSQL|PostgreSQL]] postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command. | ||
第24行: | 第30行: | ||
sudo -u postgres psql template1 | sudo -u postgres psql template1 | ||
</nowiki></pre> | </nowiki></pre> | ||
==== Gutsy/Hardy ==== | |||
<pre><nowiki> | <pre><nowiki> | ||
sudo -u postgres psql postgres | sudo -u postgres psql postgres | ||
第38行: | 第44行: | ||
sudo -u postgres createdb mydb | sudo -u postgres createdb mydb | ||
</nowiki></pre> | </nowiki></pre> | ||
=== Alternative Server Setup === | |||
If you don't intend to connect to the database from other machines, this alternative setup may be simpler. | |||
By default in Ubuntu, Postgresql is configured to use 'ident sameuser' authentication for any connections from the same machine. Check out the excellent Postgresql documentation for more information, but essentially this means that if your Ubuntu username is 'foo' and you add 'foo' as a Postgresql user then you can connect to the database without requiring a password. | |||
Since the only user who can connect to a fresh install is the postgres user, here is how to create yourself a database account (which is in this case also a database superuser) with the same name as your login name: | |||
<pre><nowiki> | |||
sudo -u postgres createuser --superuser $USER | |||
</nowiki></pre> | |||
Client programs, by default, connect to the local host using your Ubuntu login name and expect to find a database with that name too. So to make things REALLY easy, use your new superuser privileges granted above to create a database with the same name as your login name: | |||
<pre><nowiki> | |||
createdb $USER | |||
</nowiki></pre> | |||
Connecting to your own database to try out some SQL should now be as easy as: | |||
<pre><nowiki> | |||
psql | |||
</nowiki></pre> | |||
Creating additional database is just as easy, so for example, after running this: | |||
<pre><nowiki> | |||
createdb amarokdb | |||
</nowiki></pre> | |||
You can go right ahead and tell Amarok to use postgresql to store its music catalog. The database name would be amarokdb, the username would be your own login name, and you don't even need a password thanks to 'ident sameuser' so you can leave that blank. | |||
=== Using pgAdmin III GUI === | === Using pgAdmin III GUI === | ||
To get an idea of what [[UbuntuHelp:PostgreSQL|PostgreSQL]] can do, you may start by firing up a graphical client. In a terminal type : | To get an idea of what [[UbuntuHelp:PostgreSQL|PostgreSQL]] can do, you may start by firing up a graphical client. In a terminal type : | ||
第67行: | 第93行: | ||
---- | ---- | ||
<sup>[1]</sup> You do not have to pay in order to use PostgreSQL for some applications, such as commercial closed source software, contrary to other well known open source databases. | <sup>[1]</sup> You do not have to pay in order to use PostgreSQL for some applications, such as commercial closed source software, contrary to other well known open source databases. | ||
< | <<BR>> | ||
'''Note:''' this guide has been tested on Ubuntu 6.06 (Dapper) and Ubuntu 7.10 (Gutsy) | '''Note:''' this guide has been tested on Ubuntu 6.06 (Dapper) and Ubuntu 7.10 (Gutsy) | ||
---- | ---- |
2008年10月19日 (日) 16:55的版本
文章出处: |
{{#if: | {{{2}}} | https://help.ubuntu.com/community/PostgreSQL }} |
点击翻译: |
English {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/af | • {{#if: UbuntuHelp:PostgreSQL|Afrikaans| [[::PostgreSQL/af|Afrikaans]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/ar | • {{#if: UbuntuHelp:PostgreSQL|العربية| [[::PostgreSQL/ar|العربية]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/az | • {{#if: UbuntuHelp:PostgreSQL|azərbaycanca| [[::PostgreSQL/az|azərbaycanca]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/bcc | • {{#if: UbuntuHelp:PostgreSQL|جهلسری بلوچی| [[::PostgreSQL/bcc|جهلسری بلوچی]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/bg | • {{#if: UbuntuHelp:PostgreSQL|български| [[::PostgreSQL/bg|български]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/br | • {{#if: UbuntuHelp:PostgreSQL|brezhoneg| [[::PostgreSQL/br|brezhoneg]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/ca | • {{#if: UbuntuHelp:PostgreSQL|català| [[::PostgreSQL/ca|català]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/cs | • {{#if: UbuntuHelp:PostgreSQL|čeština| [[::PostgreSQL/cs|čeština]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/de | • {{#if: UbuntuHelp:PostgreSQL|Deutsch| [[::PostgreSQL/de|Deutsch]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/el | • {{#if: UbuntuHelp:PostgreSQL|Ελληνικά| [[::PostgreSQL/el|Ελληνικά]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/es | • {{#if: UbuntuHelp:PostgreSQL|español| [[::PostgreSQL/es|español]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/fa | • {{#if: UbuntuHelp:PostgreSQL|فارسی| [[::PostgreSQL/fa|فارسی]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/fi | • {{#if: UbuntuHelp:PostgreSQL|suomi| [[::PostgreSQL/fi|suomi]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/fr | • {{#if: UbuntuHelp:PostgreSQL|français| [[::PostgreSQL/fr|français]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/gu | • {{#if: UbuntuHelp:PostgreSQL|ગુજરાતી| [[::PostgreSQL/gu|ગુજરાતી]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/he | • {{#if: UbuntuHelp:PostgreSQL|עברית| [[::PostgreSQL/he|עברית]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/hu | • {{#if: UbuntuHelp:PostgreSQL|magyar| [[::PostgreSQL/hu|magyar]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/id | • {{#if: UbuntuHelp:PostgreSQL|Bahasa Indonesia| [[::PostgreSQL/id|Bahasa Indonesia]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/it | • {{#if: UbuntuHelp:PostgreSQL|italiano| [[::PostgreSQL/it|italiano]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/ja | • {{#if: UbuntuHelp:PostgreSQL|日本語| [[::PostgreSQL/ja|日本語]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/ko | • {{#if: UbuntuHelp:PostgreSQL|한국어| [[::PostgreSQL/ko|한국어]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/ksh | • {{#if: UbuntuHelp:PostgreSQL|Ripoarisch| [[::PostgreSQL/ksh|Ripoarisch]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/mr | • {{#if: UbuntuHelp:PostgreSQL|मराठी| [[::PostgreSQL/mr|मराठी]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/ms | • {{#if: UbuntuHelp:PostgreSQL|Bahasa Melayu| [[::PostgreSQL/ms|Bahasa Melayu]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/nl | • {{#if: UbuntuHelp:PostgreSQL|Nederlands| [[::PostgreSQL/nl|Nederlands]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/no | • {{#if: UbuntuHelp:PostgreSQL|norsk| [[::PostgreSQL/no|norsk]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/oc | • {{#if: UbuntuHelp:PostgreSQL|occitan| [[::PostgreSQL/oc|occitan]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/pl | • {{#if: UbuntuHelp:PostgreSQL|polski| [[::PostgreSQL/pl|polski]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/pt | • {{#if: UbuntuHelp:PostgreSQL|português| [[::PostgreSQL/pt|português]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/ro | • {{#if: UbuntuHelp:PostgreSQL|română| [[::PostgreSQL/ro|română]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/ru | • {{#if: UbuntuHelp:PostgreSQL|русский| [[::PostgreSQL/ru|русский]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/si | • {{#if: UbuntuHelp:PostgreSQL|සිංහල| [[::PostgreSQL/si|සිංහල]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/sq | • {{#if: UbuntuHelp:PostgreSQL|shqip| [[::PostgreSQL/sq|shqip]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/sr | • {{#if: UbuntuHelp:PostgreSQL|српски / srpski| [[::PostgreSQL/sr|српски / srpski]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/sv | • {{#if: UbuntuHelp:PostgreSQL|svenska| [[::PostgreSQL/sv|svenska]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/th | • {{#if: UbuntuHelp:PostgreSQL|ไทย| [[::PostgreSQL/th|ไทย]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/tr | • {{#if: UbuntuHelp:PostgreSQL|Türkçe| [[::PostgreSQL/tr|Türkçe]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/vi | • {{#if: UbuntuHelp:PostgreSQL|Tiếng Việt| [[::PostgreSQL/vi|Tiếng Việt]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/yue | • {{#if: UbuntuHelp:PostgreSQL|粵語| [[::PostgreSQL/yue|粵語]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/zh | • {{#if: UbuntuHelp:PostgreSQL|中文| [[::PostgreSQL/zh|中文]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/zh-hans | • {{#if: UbuntuHelp:PostgreSQL|中文(简体)| [[::PostgreSQL/zh-hans|中文(简体)]]}}|}} {{#ifexist: {{#if: UbuntuHelp:PostgreSQL | UbuntuHelp:PostgreSQL | {{#if: | :}}PostgreSQL}}/zh-hant | • {{#if: UbuntuHelp:PostgreSQL|中文(繁體)| [[::PostgreSQL/zh-hant|中文(繁體)]]}}|}} |
{{#ifeq:UbuntuHelp:PostgreSQL|:PostgreSQL|请不要直接编辑翻译本页,本页将定期与来源同步。}} |
{{#ifexist: :PostgreSQL/zh | | {{#ifexist: PostgreSQL/zh | | {{#ifeq: {{#titleparts:PostgreSQL|1|-1|}} | zh | | }} }} }} {{#ifeq: {{#titleparts:PostgreSQL|1|-1|}} | zh | | }}
Introduction
PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license.[1] PostgreSQL contains many advanced features, is very fast and standards compliant. PostgreSQL has bindings for many programming languages such as C, C++, Python, Java, PHP, Ruby... It can be used to power anything from simple web applications to massive databases with millions of records.
Installation
Dapper
To install PostgreSQL 8.1 you may use the command line and type :
sudo apt-get install postgresql-8.1
pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install type at the command line :
sudo apt-get install pgadmin3
You may also use the Synaptic package manager from the System>Administration menu to install these packages.
Gutsy
To install Postgresql-8.2 using your favorite package manager install the postgresql package.
Hardy
Install the latest Postgresql (v8.3) from the command line with the following command:
sudo apt-get install postgresql
Or you can use synaptic/adept to install the postgresql package.
Basic Server Setup
To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command. In a terminal, type:
Dapper
sudo -u postgres psql template1
Gutsy/Hardy
sudo -u postgres psql postgres
Then, at the new prompt type these two commands, replacing <***password***> with the new password (keep this safe ;) ) :
ALTER USER postgres WITH ENCRYPTED PASSWORD ' <***password***> '; \q
Create database
To create the first database, which we will call "mydb", simply type :
sudo -u postgres createdb mydb
Alternative Server Setup
If you don't intend to connect to the database from other machines, this alternative setup may be simpler. By default in Ubuntu, Postgresql is configured to use 'ident sameuser' authentication for any connections from the same machine. Check out the excellent Postgresql documentation for more information, but essentially this means that if your Ubuntu username is 'foo' and you add 'foo' as a Postgresql user then you can connect to the database without requiring a password. Since the only user who can connect to a fresh install is the postgres user, here is how to create yourself a database account (which is in this case also a database superuser) with the same name as your login name:
sudo -u postgres createuser --superuser $USER
Client programs, by default, connect to the local host using your Ubuntu login name and expect to find a database with that name too. So to make things REALLY easy, use your new superuser privileges granted above to create a database with the same name as your login name:
createdb $USER
Connecting to your own database to try out some SQL should now be as easy as:
psql
Creating additional database is just as easy, so for example, after running this:
createdb amarokdb
You can go right ahead and tell Amarok to use postgresql to store its music catalog. The database name would be amarokdb, the username would be your own login name, and you don't even need a password thanks to 'ident sameuser' so you can leave that blank.
Using pgAdmin III GUI
To get an idea of what PostgreSQL can do, you may start by firing up a graphical client. In a terminal type :
pgadmin3
You will be presented with the pgAdmin III interface. Click on the "Add a connection to a server" button (top left). In the new dialog, enter the address 127.0.0.1, a description of the server, the default database ("mydb" in the example above), your username ("postgres") and your password. With this GUI you may start creating and managing databases, query the database, execute SQl etc.
Managing the Server
Managing users and rights
PostgreSQL has a not really easy way to manage users. To manage users, you first have to edit /etc/postgresql/8.1/main/pg_hba.conf
and modify the default configuration which is very protective. For example, if you want postgres
to manage its own users (not linked with system users), you will add the following line:
8<------------------------------------------- # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD host all all 10.0.0.0 255.255.255.0 md5 8<-------------------------------------------
Which means that on your local network (10.0.0.0/24 - replace with your own local network !), postgres users can connect through the network to the database providing a classical couple user / password. To create a database with a user that have full rights on the database, use the following command:
sudo -u postgres createuser -D -A -P myuser sudo -u postgres createdb -O myuser mydb
The first command line creates the user the no database creation rights (-D) with no add user rights -A) and will prompt you for entering a password (-P). The second command line create the database 'mydb with 'myuser' as owner. This little example will probably suit most of your needs. For more details, please refer to the corresponding man pages or the online documentation.
Further reading
If you are not familiar with SQL you may want to look into this powerful language, although some simple uses of PostgreSQL may not require this knowledge (such as a simple Django project). The PostgreSQL website contains a wealth of information on using this database.
[1] You do not have to pay in order to use PostgreSQL for some applications, such as commercial closed source software, contrary to other well known open source databases.
<
>
Note: this guide has been tested on Ubuntu 6.06 (Dapper) and Ubuntu 7.10 (Gutsy)