PostgreSQL新手教程

ubuntuq@ubuntuq-HP-Compaq-6910p-RH241AV:~$ sudo apt-get install postgresql-client[sudo] password for ubuntuq: Sorry, try again.[sudo] password for ubuntuq: Reading package lists... DoneBuilding dependency tree       Reading state information... DoneThe following packages were automatically installed and are no longer required:  fcitx-frontend-gtk2:i386 fcitx-frontend-gtk3:i386 fcitx-frontend-qt4:i386  fcitx-libs:i386 fcitx-libs-gclient:i386 fcitx-libs-qt:i386 gcc-4.8-base:i386  kde-l10n-engb kde-l10n-zhcn libasound2:i386 libatk-bridge2.0-0:i386  libatk1.0-0:i386 libatspi2.0-0:i386 libaudio2:i386 libavahi-client3:i386  libavahi-common-data:i386 libavahi-common3:i386 libcairo-gobject2:i386  libcairo2:i386 libcanberra-gtk3-0:i386 libcanberra-gtk3-module:i386  libcanberra0:i386 libcolord1:i386 libcups2:i386 libdatrie1:i386  libexpat1:i386 libffi6:i386 libfontconfig1:i386 libfreetype6:i386  libgcrypt11:i386 libgdk-pixbuf2.0-0:i386 libglib2.0-0:i386 libgnutls26:i386  libgpg-error0:i386 libgraphite2-3:i386 libgssapi-krb5-2:i386 libgtk-3-0:i386  libgtk2.0-0:i386 libharfbuzz0b:i386 libice6:i386 libidn11:i386  libjasper1:i386 libjbig0:i386 libjpeg-turbo8:i386 libjpeg8:i386  libk5crypto3:i386 libkeyutils1:i386 libkrb5-3:i386 libkrb5support0:i386  liblcms2-2:i386 libltdl7:i386 libmysqlclient18:i386 libnotify4:i386  libogg0:i386 libopencc1:i386 libp11-kit0:i386 libpango-1.0-0:i386  libpangocairo-1.0-0:i386 libpangoft2-1.0-0:i386 libpixman-1-0:i386  libqt4-dbus:i386 libqt4-declarative:i386 libqt4-network:i386  libqt4-script:i386 libqt4-sql:i386 libqt4-sql-mysql:i386 libqt4-xml:i386  libqt4-xmlpatterns:i386 libqtcore4:i386 libqtdbus4:i386 libqtgui4:i386  libsm6:i386 libstdc++6:i386 libtasn1-6:i386 libtdb1:i386 libthai0:i386  libtiff5:i386 libvorbis0a:i386 libvorbisfile3:i386 libwayland-client0:i386  libwayland-cursor0:i386 libx11-6:i386 libxau6:i386 libxcb-render0:i386  libxcb-shm0:i386 libxcb1:i386 libxcomposite1:i386 libxcursor1:i386  libxdamage1:i386 libxdmcp6:i386 libxext6:i386 libxfixes3:i386 libxi6:i386  libxinerama1:i386 libxkbcommon0:i386 libxrandr2:i386 libxrender1:i386  libxt6:i386 notification-daemon:i386Use 'apt-get autoremove' to remove them.The following NEW packages will be installed:  postgresql-client0 upgraded, 1 newly installed, 0 to remove and 240 not upgraded.Need to get 5,052 B of archives.After this operation, 72.7 kB of additional disk space will be used.Get:1 http://cn.archive.ubuntu.com/ubuntu/ trusty-updates/main postgresql-client all 9.3+154ubuntu1 [5,052 B]Fetched 5,052 B in 0s (74.0 kB/s)      Selecting previously unselected package postgresql-client.(Reading database ... 215144 files and directories currently installed.)Preparing to unpack .../postgresql-client_9.3+154ubuntu1_all.deb ...Unpacking postgresql-client (9.3+154ubuntu1) ...Setting up postgresql-client (9.3+154ubuntu1) ...ubuntuq@ubuntuq-HP-Compaq-6910p-RH241AV:~$ sudo apt-get install postgresqlReading package lists... DoneBuilding dependency tree       Reading state information... Donepostgresql is already the newest version.The following packages were automatically installed and are no longer required:  fcitx-frontend-gtk2:i386 fcitx-frontend-gtk3:i386 fcitx-frontend-qt4:i386  fcitx-libs:i386 fcitx-libs-gclient:i386 fcitx-libs-qt:i386 gcc-4.8-base:i386  kde-l10n-engb kde-l10n-zhcn libasound2:i386 libatk-bridge2.0-0:i386  libatk1.0-0:i386 libatspi2.0-0:i386 libaudio2:i386 libavahi-client3:i386  libavahi-common-data:i386 libavahi-common3:i386 libcairo-gobject2:i386  libcairo2:i386 libcanberra-gtk3-0:i386 libcanberra-gtk3-module:i386  libcanberra0:i386 libcolord1:i386 libcups2:i386 libdatrie1:i386  libexpat1:i386 libffi6:i386 libfontconfig1:i386 libfreetype6:i386  libgcrypt11:i386 libgdk-pixbuf2.0-0:i386 libglib2.0-0:i386 libgnutls26:i386  libgpg-error0:i386 libgraphite2-3:i386 libgssapi-krb5-2:i386 libgtk-3-0:i386  libgtk2.0-0:i386 libharfbuzz0b:i386 libice6:i386 libidn11:i386  libjasper1:i386 libjbig0:i386 libjpeg-turbo8:i386 libjpeg8:i386  libk5crypto3:i386 libkeyutils1:i386 libkrb5-3:i386 libkrb5support0:i386  liblcms2-2:i386 libltdl7:i386 libmysqlclient18:i386 libnotify4:i386  libogg0:i386 libopencc1:i386 libp11-kit0:i386 libpango-1.0-0:i386  libpangocairo-1.0-0:i386 libpangoft2-1.0-0:i386 libpixman-1-0:i386  libqt4-dbus:i386 libqt4-declarative:i386 libqt4-network:i386  libqt4-script:i386 libqt4-sql:i386 libqt4-sql-mysql:i386 libqt4-xml:i386  libqt4-xmlpatterns:i386 libqtcore4:i386 libqtdbus4:i386 libqtgui4:i386  libsm6:i386 libstdc++6:i386 libtasn1-6:i386 libtdb1:i386 libthai0:i386  libtiff5:i386 libvorbis0a:i386 libvorbisfile3:i386 libwayland-client0:i386  libwayland-cursor0:i386 libx11-6:i386 libxau6:i386 libxcb-render0:i386  libxcb-shm0:i386 libxcb1:i386 libxcomposite1:i386 libxcursor1:i386  libxdamage1:i386 libxdmcp6:i386 libxext6:i386 libxfixes3:i386 libxi6:i386  libxinerama1:i386 libxkbcommon0:i386 libxrandr2:i386 libxrender1:i386  libxt6:i386 notification-daemon:i386Use 'apt-get autoremove' to remove them.0 upgraded, 0 newly installed, 0 to remove and 240 not upgraded.ubuntuq@ubuntuq-HP-Compaq-6910p-RH241AV:~$ sudo apt-get install pgadmin3Reading package lists... DoneBuilding dependency tree       Reading state information... DoneThe following packages were automatically installed and are no longer required:  fcitx-frontend-gtk2:i386 fcitx-frontend-gtk3:i386 fcitx-frontend-qt4:i386  fcitx-libs:i386 fcitx-libs-gclient:i386 fcitx-libs-qt:i386 gcc-4.8-base:i386  kde-l10n-engb kde-l10n-zhcn libasound2:i386 libatk-bridge2.0-0:i386  libatk1.0-0:i386 libatspi2.0-0:i386 libaudio2:i386 libavahi-client3:i386  libavahi-common-data:i386 libavahi-common3:i386 libcairo-gobject2:i386  libcairo2:i386 libcanberra-gtk3-0:i386 libcanberra-gtk3-module:i386  libcanberra0:i386 libcolord1:i386 libcups2:i386 libdatrie1:i386  libexpat1:i386 libffi6:i386 libfontconfig1:i386 libfreetype6:i386  libgcrypt11:i386 libgdk-pixbuf2.0-0:i386 libglib2.0-0:i386 libgnutls26:i386  libgpg-error0:i386 libgraphite2-3:i386 libgssapi-krb5-2:i386 libgtk-3-0:i386  libgtk2.0-0:i386 libharfbuzz0b:i386 libice6:i386 libidn11:i386  libjasper1:i386 libjbig0:i386 libjpeg-turbo8:i386 libjpeg8:i386  libk5crypto3:i386 libkeyutils1:i386 libkrb5-3:i386 libkrb5support0:i386  liblcms2-2:i386 libltdl7:i386 libmysqlclient18:i386 libnotify4:i386  libogg0:i386 libopencc1:i386 libp11-kit0:i386 libpango-1.0-0:i386  libpangocairo-1.0-0:i386 libpangoft2-1.0-0:i386 libpixman-1-0:i386  libqt4-dbus:i386 libqt4-declarative:i386 libqt4-network:i386  libqt4-script:i386 libqt4-sql:i386 libqt4-sql-mysql:i386 libqt4-xml:i386  libqt4-xmlpatterns:i386 libqtcore4:i386 libqtdbus4:i386 libqtgui4:i386  libsm6:i386 libstdc++6:i386 libtasn1-6:i386 libtdb1:i386 libthai0:i386  libtiff5:i386 libvorbis0a:i386 libvorbisfile3:i386 libwayland-client0:i386  libwayland-cursor0:i386 libx11-6:i386 libxau6:i386 libxcb-render0:i386  libxcb-shm0:i386 libxcb1:i386 libxcomposite1:i386 libxcursor1:i386  libxdamage1:i386 libxdmcp6:i386 libxext6:i386 libxfixes3:i386 libxi6:i386  libxinerama1:i386 libxkbcommon0:i386 libxrandr2:i386 libxrender1:i386  libxt6:i386 notification-daemon:i386Use 'apt-get autoremove' to remove them.The following extra packages will be installed:  libwxbase2.8-0 libwxgtk2.8-0 pgadmin3-data pgagentSuggested packages:  postgresql-contribThe following NEW packages will be installed:  libwxbase2.8-0 libwxgtk2.8-0 pgadmin3 pgadmin3-data pgagent0 upgraded, 5 newly installed, 0 to remove and 240 not upgraded.Need to get 6,798 kB of archives.After this operation, 30.0 MB of additional disk space will be used.Do you want to continue? [Y/n] yGet:1 http://cn.archive.ubuntu.com/ubuntu/ trusty/universe libwxbase2.8-0 amd64 2.8.12.1+dfsg-2ubuntu2 [460 kB]Get:2 http://cn.archive.ubuntu.com/ubuntu/ trusty/universe libwxgtk2.8-0 amd64 2.8.12.1+dfsg-2ubuntu2 [2,371 kB]Get:3 http://cn.archive.ubuntu.com/ubuntu/ trusty/universe pgadmin3-data all 1.18.1-2 [1,823 kB]Get:4 http://cn.archive.ubuntu.com/ubuntu/ trusty/universe pgadmin3 amd64 1.18.1-2 [2,104 kB]Get:5 http://cn.archive.ubuntu.com/ubuntu/ trusty/universe pgagent amd64 3.3.0-1 [39.8 kB]Fetched 6,798 kB in 8s (821 kB/s)                                              Selecting previously unselected package libwxbase2.8-0:amd64.(Reading database ... 215147 files and directories currently installed.)Preparing to unpack .../libwxbase2.8-0_2.8.12.1+dfsg-2ubuntu2_amd64.deb ...Unpacking libwxbase2.8-0:amd64 (2.8.12.1+dfsg-2ubuntu2) ...Selecting previously unselected package libwxgtk2.8-0:amd64.Preparing to unpack .../libwxgtk2.8-0_2.8.12.1+dfsg-2ubuntu2_amd64.deb ...Unpacking libwxgtk2.8-0:amd64 (2.8.12.1+dfsg-2ubuntu2) ...Selecting previously unselected package pgadmin3-data.Preparing to unpack .../pgadmin3-data_1.18.1-2_all.deb ...Unpacking pgadmin3-data (1.18.1-2) ...Selecting previously unselected package pgadmin3.Preparing to unpack .../pgadmin3_1.18.1-2_amd64.deb ...Unpacking pgadmin3 (1.18.1-2) ...Selecting previously unselected package pgagent.Preparing to unpack .../pgagent_3.3.0-1_amd64.deb ...Unpacking pgagent (3.3.0-1) ...Processing triggers for doc-base (0.10.5) ...Processing 1 added doc-base file...Processing triggers for gnome-menus (3.10.1-0ubuntu2) ...Processing triggers for desktop-file-utils (0.22-1ubuntu1) ...Processing triggers for bamfdaemon (0.5.1+14.04.20140409-0ubuntu1) ...Rebuilding /usr/share/applications/bamf-2.index...Processing triggers for mime-support (3.54ubuntu1.1) ...Processing triggers for man-db (2.6.7.1-1ubuntu1) ...Setting up libwxbase2.8-0:amd64 (2.8.12.1+dfsg-2ubuntu2) ...Setting up libwxgtk2.8-0:amd64 (2.8.12.1+dfsg-2ubuntu2) ...Setting up pgadmin3-data (1.18.1-2) ...Setting up pgadmin3 (1.18.1-2) ...Setting up pgagent (3.3.0-1) ...Processing triggers for libc-bin (2.19-0ubuntu6.7) ...ubuntuq@ubuntuq-HP-Compaq-6910p-RH241AV:~$ sudo adduser geekleeAdding user `geeklee' ...Adding new group `geeklee' (1002) ...Adding new user `geeklee' (1002) with group `geeklee' ...Creating home directory `/home/geeklee' ...Copying files from `/etc/skel' ...Enter new UNIX password: Retype new UNIX password: passwd: password updated successfullyChanging the user information for geekleeEnter the new value, or press ENTER for the default    Full Name []:     Room Number []:     Work Phone []:     Home Phone []:     Other []: Is the information correct? [Y/n] yubuntuq@ubuntuq-HP-Compaq-6910p-RH241AV:~$ sudo su-postgressudo: su-postgres: command not foundubuntuq@ubuntuq-HP-Compaq-6910p-RH241AV:~$ sudo su - postgrespostgres@ubuntuq-HP-Compaq-6910p-RH241AV:~$ psqlpsql (9.3.11)Type "help" for help.postgres=# \password postgresEnter new password: Enter it again: postgres=# CREATE USER dbuser WITH PASSWORD 'password'postgres-# CREATE USER geeklee WITH PASSWORD 'password'postgres-# CREATE DATABASE exampledb OWNER geekleepostgres-# GRANT ALL PRIVILEGES ON DATABASE exampledb to geekleepostgres-# \q

安装

1首先，安装PostgreSQL客户端。

    sudo apt-get install postgresql-client

然后，安装PostgreSQL服务器。

    sudo apt-get install postgresql

2正常情况下，安装完成后，PostgreSQL服务器会自动在本机的5432端口开启。如果还想安装图形管理界面，可以运行下面命令，但是本文不涉及这方面内容。

    sudo apt-get install pgadmin3

添加新用户和新数据库

初次安装后，默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是，同时还生成了一个名为postgres的Linux系统用户。

下面，我们使用postgres用户，来生成其他用户和新数据库。好几种方法可以达到这个目的，这里介绍两种。

第一种方法，使用PostgreSQL控制台。

首先，新建一个Linux新用户，可以取你想要的名字，这里为dbuser。

sudo adduser dbuser  #geeklee 

然后，切换到postgres用户。

sudo su - postgres

下一步，使用psql命令登录PostgreSQL控制台。

psql

这时相当于系统用户postgres以同名数据库用户的身份，登录数据库，这是不用输入密码的。如果一切正常，系统提示符会变为”postgres=#”，表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。

第一件事是使用\password命令，为postgres用户设置一个密码。

\password postgres

第二件事是创建数据库用户dbuser（刚才创建的是Linux系统用户），并设置密码。

CREATE USER geeklee WITH PASSWORD 'password';

第三件事是创建用户数据库，这里为exampledb，并指定所有者为dbuser。

CREATE DATABASE exampledb OWNER geeklee

第四件事是将exampledb数据库的所有权限都赋予dbuser，否则dbuser只能登录控制台，没有任何数据库操作权限。

GRANT ALL PRIVILEGES ON DATABASE exampledb to geeklee

最后，使用\q命令退出控制台（也可以直接按ctrl+D）。

\q

登录数据库

添加新用户和新数据库以后，就要以新用户的名义登录数据库，这时使用的是psql命令。

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

上面命令的参数含义如下：-U指定用户，-d指定数据库，-h指定服务器，-p指定端口。

输入上面命令以后，系统会提示输入dbuser用户的密码。输入正确，就可以登录控制台了。

psql命令存在简写形式。如果当前Linux系统用户，同时也是PostgreSQL用户，则可以省略用户名（-U参数的部分）。举例来说，我的 Linux系统用户名为ruanyf，且PostgreSQL数据库存在同名用户，则我以ruanyf身份登录Linux系统后，可以直接使用下面的命令 登录数据库，且不需要密码。

psql exampledb

此时，如果PostgreSQL内部还存在与当前系统用户同名的数据库，则连数据库名都可以省略。比如，假定存在一个叫做ruanyf的数据库，则直接键入psql就可以登录该数据库。

psql

另外，如果要恢复外部数据，可以使用下面的命令。

psql exampledb < exampledb.sql