失眠网 > 安装配置postgreSQL+pgcli+pgadmin3

安装配置postgreSQL+pgcli+pgadmin3

时间：2021-08-13 19:29:14

记录了postgreSQL数据库的完整的安装配置过程，以及postgreSQL的pgcli命令行智能提醒扩展，pgadmin3图形化管理客户端的配置安装。此postgresql是bigsql版安装详情，另bigsql刚出新版9.6

1.环境前提

linux（ubuntu16.04）postgresql-95

在postgreSQL官网下载deb安装包

2.安装postgresql,使用dkpg命令进行安装

sudo dpkg -i /home/wxl/Downloads/postgresql-9.5.3-1-x64-bigsql.deb

3.配置并初始化postgreSQL 服务

3.1.开启服务

hwclock.sh rc x11-commonwxl@wxl-pc:/etc/init.d$ sudo /etc/init.d/postgresql-95 startPostgreSQL 9.5 Configuration---------------------------------This will initialize PostgreSQL database cluster. The followingquestions will determine the database server port, superuser passwordand to start server on system boot. Press <ENTER> to accept defaults.Specify PostgreSQL server port [5432]:Specify superuser password [password]:Do you want PostgreSQL server to be started on boot (y/n) [y]: nuseradd: warning: the home directory already exists.Not copying any file from skel directory into it.The files belonging to this database system will be owned by user "postgres".This user must also own the server process.The database cluster will be initialized with localesCOLLATE: en_US.UTF-8CTYPE: en_US.UTF-8MESSAGES: en_US.UTF-8MONETARY: zh_CN.UTF-8NUMERIC: zh_CN.UTF-8TIME:zh_CN.UTF-8The default text search configuration will be set to "english".Data page checksums are disabled.fixing permissions on existing directory /opt/postgresql/pg95/data ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting dynamic shared memory implementation ... posixcreating configuration files ... okcreating template1 database in /opt/postgresql/pg95/data/base/1 ... okinitializing pg_authid ... oksetting password ... okinitializing dependencies ... okcreating system views ... okloading system objects descriptions ... okcreating collations ... okcreating conversions ... okcreating dictionaries ... oksetting privileges on built-in objects ... okcreating information schema ... okloading PL/pgSQL server-side language ... okvacuuming database template1 ... okcopying template1 to template0 ... okcopying template1 to postgres ... oksyncing data to disk ... okSuccess. You can now start the database server using:/opt/postgresql/pg95/bin/pg_ctl -D /opt/postgresql/pg95/data -l logfile startStarting PostgreSQL 9.5:waiting for server to start.... doneserver startedPostgreSQL 9.5 started successfullyTo load this postgres into your environment, source the env file:. /opt/postgresql/pg95/pg95.env

3.2.配置系统变量,让postgreSQL环境变量生效

a.拷贝/opt/postgresql/pg95/pg95.env并重名为postgresql.sh,将postgreSQL.sh放置/etc/profile.d/下

wxl@wxl-pc: sudo cp /opt/postgresql/pg95/pg95.env /etc/profile.d/postgresql.sh

b.让变量生效

wxl@wxl-pc: source /etc/profile

3.3.进入postgresql 密码默认是`password`可以直接`psql`也可以`psql -U postgres -d postgres`进入sql

wxl@wxl-pc:~$ psqlPassword:psql (9.5.3)Type "help" for help.postgres=# helpYou are using psql, the command-line interface to PostgreSQL.Type: \copyright for distribution terms\h for help with SQL commands\? for help with psql commands\g or terminate with semicolon to execute query\q to quitpostgres=# \q

3.4.修改默认密码(默认用户名是 postgres,密码是 postgres)

ALTER USER postgres WITH PASSWORD 'newpassword';

wxl@wxl-pc:~$ psql -U postgres -d postgresPassword for user postgres:psql (9.5.3)Type "help" for help.postgres=# ALTER USER postgres WITH PASSWORD 'newpassword';ALTER ROLEpostgres=# \q

4.安装图形界面

4.1.快速安装安装pgcli

a.安装pip

wxl@wxl-pc:~$ sudo apt install python-pip

b.通过pip快速安装pgcli

wxl@wxl-pc:~$ sudo pip install pgcli

c.如果!!如下报错,因为没有写入路径的权限，加sudo

wxl@wxl-pc:~$ pip install pgcli

Collecting pgcliUsing cached pgcli-1.1.0.tar.gzCollecting pgspecial>=1.5.0 (from pgcli)Using cached pgspecial-1.5.0.tar.gzCollecting click>=4.1 (from pgcli)Using cached click-6.6.tar.gzRequirement already satisfied (use --upgrade to upgrade): Pygments>=2.0 in /usr/local/lib/python2.7/dist-packages (from pgcli)Requirement already satisfied (use --upgrade to upgrade): prompt_toolkit<1.1.0,>=1.0.0 in /usr/local/lib/python2.7/dist-packages (from pgcli)Collecting psycopg2>=2.5.4 (from pgcli)Using cached psycopg2-2.6.2.tar.gzComplete output from command python setup.py egg_info:running egg_infocreating pip-egg-info/psycopg2.egg-infowriting pip-egg-info/psycopg2.egg-info/PKG-INFOwriting top-level names to pip-egg-info/psycopg2.egg-info/top_level.txtwriting dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txtwriting manifest file 'pip-egg-info/psycopg2.egg-info/SOURCES.txt'warning: manifest_maker: standard file '-c' not foundError: pg_config executable not found.Please add the directory containing pg_config to the PATHor specify the full executable path with the option:python setup.py build_ext --pg-config /path/to/pg_config build ...or with the pg_config option in 'setup.cfg'.----------------------------------------Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-sD12wU/psycopg2/