安装配置postgreSQL+pgcli+pgadmin3

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

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-common
wxl@wxl-pc:/etc/init.d$ sudo /etc/init.d/postgresql-95 start
PostgreSQL 9.5 Configuration
---------------------------------
This will initialize PostgreSQL database cluster. The following
questions will determine the database server port, superuser password
 and 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]: n
useradd: 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 locales
  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: zh_CN.UTF-8
  NUMERIC:  zh_CN.UTF-8
  TIME:     zh_CN.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /opt/postgresql/pg95/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /opt/postgresql/pg95/data/base/1 ... ok
initializing pg_authid ... ok
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /opt/postgresql/pg95/bin/pg_ctl -D /opt/postgresql/pg95/data -l logfile start

Starting PostgreSQL 9.5:
waiting for server to start.... done
server started
PostgreSQL 9.5 started successfully
To 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:~$ psql
Password:
psql (9.5.3)
Type "help" for help.

postgres=# help
You 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 quit
postgres=# \q

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

ALTER USER postgres WITH PASSWORD 'newpassword';
wxl@wxl-pc:~$ psql -U postgres -d postgres
Password for user postgres:
psql (9.5.3)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'newpassword';
ALTER ROLE
postgres=# \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 pgcli
  Using cached pgcli-1.1.0.tar.gz
Collecting pgspecial>=1.5.0 (from pgcli)
  Using cached pgspecial-1.5.0.tar.gz
Collecting click>=4.1 (from pgcli)
  Using cached click-6.6.tar.gz
Requirement 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.gz
    Complete output from command python setup.py egg_info:
    running egg_info
    creating pip-egg-info/psycopg2.egg-info
    writing pip-egg-info/psycopg2.egg-info/PKG-INFO
    writing top-level names to pip-egg-info/psycopg2.egg-info/top_level.txt
    writing dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txt
    writing manifest file 'pip-egg-info/psycopg2.egg-info/SOURCES.txt'
    warning: manifest_maker: standard file '-c' not found

    Error: pg_config executable not found.

    Please add the directory containing pg_config to the PATH
    or 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/

4.2.验证postgreSQL安装是否正确,并查看pgcli版本

a.开启postgreSQL

#开启postgreSQL
sudo /etc/init.d/postgresql-95 start
#环境变量生效
wxl@wxl-pc:~$ . /opt/postgresql/pg95/pg95.env

b.查看pgcli版本

wxl@wxl-pc:~$ pgcli
Password:
Version: 1.1.0
Chat: https://gitter.im/dbcli/pgcli
Mail: https://groups.google.com/forum/#!forum/pgcli
Home: http://pgcli.com
postgres>

5.安装pgadmin3可视化数据库管理工具

5.1.pgadmin3安装

wxl@wxl-pc:~$ sudo install pgadmin3

5.2.pdadmin3链接postgresql

正确填写信息 name,host,password等信息

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏行者悟空

Apache Hive 安装详解及相关问题解决方案

1323
来自专栏运维

Puppet3.1 Master Client安装测试

Linux master.inno.com 2.6.32-279.22.1.el6.centos.plus.x86_64 #1 SMP Wed Feb 6 05...

752
来自专栏清风

Redhat 6 升级 openssl-1.0.2m 、openssh-7.6p1 和 ntp-4.2.8p10

本文涉及rpm源码包下载地址:http://mirrors.163.com/centos/6/os/x86_64/Packages

1627
来自专栏Hadoop实操

如何在Redhat7.4安装HDP3.0.1

7月13日,Hortonworks在其官网宣布发布HDP3.0,相关介绍可以参考Fayson昨天的文章《Hortonworks正式发布HDP3.0》,最近又更新...

1894
来自专栏Aloys的开发之路

如何利用Oracle VM Templates 在几分钟内部署Oracle Real Application Clusters (RAC)

 本文自发布后有不少更改,但由于图片较多,cnblogs支持不完全,请到<https://wenku.baidu.com/view/2c03933f59fafa...

2017
来自专栏写代码的海盗

使用apt-get 安装后的mysql 登录问题

当使用apt-get安装mysql后,ubuntu会自动生成一个用户名和密码。所以在第一次登陆时会报如下错误 ERROR 1045 (28000): Acces...

2748
来自专栏云计算

使用Postfix,Dovecot和MySQL发送电子邮件

在本指南中,您将学习如何在Debian或Ubuntu上使用Postfix,Dovecot和MySQL设置安全的虚拟用户邮件服务器。我们将解释如何创建新的用户邮箱...

1192
来自专栏菩提树下的杨过

Oracle XE http端口8080的修改

Oracle Express Edition(XE)默认的http端口是8080,这跟JBoss/Tomcat的默认端口相同,导致Jboss启动冲突。 修改办法...

2039
来自专栏bdcn

CoreOS配置Docker API TLS认证 顶

我们经常会利用Portainer来管理docker环境,也经常会用Jenkins来自动构建和部署docker,远程管理都会使用到Docker API,通常我们只...

1022
来自专栏bboysoul

dvwa安装教程

DVWA (Damn Vulnerable Web Application) 是用 PHP+MySQL 编写的一套用于漏洞检测和教学的程序,支持多种数据库,包括...

1173

扫码关注云+社区