postgres=#1.4、修改管理员密码修改PostgreSQL 数据库用户postgres的密码（注意不是linux系统帐号） PostgreSQL 数据库默认会创建一个postgres的数据库用户作为数据库的管理员，默认密码为空，我们需要修改为指定的密码，这里设定为’postgres’。 postgres=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+--------+----------+----------- postgres | 10 | t | t | t | t | | | （1 行记录）
postgres=# ALTER USER postgres WITH PASSWORD "postgres"; ALTER ROLE postgres=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+----------- postgres | 10 | t | t | t | t | md53175bce1d3201d16594cebf9d7eb3f9d | | （1 行记录）
postgres=#1.5、创建测试数据库 postgres=# create database testdb; CREATE DATABASE postgres=# c testdb; 您现在已经连线到数据库 "testdb",用户 "postgres". testdb=#1.6、创建测试表 testdb=# create table test （id integer, name text）; CREATE TABLE testdb=# insert into test values（1,"lansgg"）; INSERT 0 1 testdb=# select * from test; id | name ----+-------- 1 | lansgg （1 行记录）1.7、查看表结构 testdb=# d test; 资料表 "public.test" 栏位 | 型别 | 修饰词 ------+---------+-------- id | integer | name | text |1.8、修改PostgresSQL 数据库配置实现远程访问 修改postgresql.conf 文件 如果想让PostgreSQL 监听整个网络的话，将listen_addresses 前的#去掉，并将 listen_addresses = "localhost" 改成 listen_addresses = "*" 修改客户端认证配置文件pg_hba.conf [root@vm2 ~]# vim /var/lib/pgsql/9.2/data/pg_hba.conf host all all 127.0.0.1/32 ident host all all all md5[root@vm2 ~]# /etc/init.d/postgresql-9.2 restart 停止 postgresql-9.2 服务： [确定] 启动 postgresql-9.2 服务： [确定] [root@vm2 ~]#2、源码安装 停止上面yum安装的pgsql服务，下载PostgreSQL 源码包 [root@vm2 ~]# /etc/init.d/postgresql-9.2 stop 停止 postgresql-9.2 服务： [确定] [root@vm2 ~]# wget [root@vm2 ~]# tar jxvf postgresql-9.2.4.tar.bz2 [root@vm2 ~]# cd postgresql-9.2.4查看INSTALL 文件 more INSTALL INSTALL 文件中Short Version 部分解释了如何安装PostgreSQL 的命令，Requirements 部分描述了安装PostgreSQL 所依赖的lib，比较长，先configure 试一下，如果出现error，那么需要检查是否满足了Requirements 的要求。 开始编译安装PostgreSQL 数据库。 [root@vm2 postgresql-9.2.4]# ./configure [root@vm2 postgresql-9.2.4]# gmake [root@vm2 postgresql-9.2.4]# gmake install [root@vm2 postgresql-9.2.4]# echo "PGHOME=/usr/local/pgsql" >> /etc/profile [root@vm2 postgresql-9.2.4]# echo "export PGHOME" >> /etc/profile [root@vm2 postgresql-9.2.4]# echo "PGDATA=/usr/local/pgsql/data" >> /etc/profile [root@vm2 postgresql-9.2.4]# echo "export PGDATA" >> /etc/profile [root@vm2 postgresql-9.2.4]# echo "PATH=$PGHOME/bin:$PATH" >> /etc/profile [root@vm2 postgresql-9.2.4]# echo "export PATH" >> /etc/profile [root@vm2 postgresql-9.2.4]# source /etc/profile [root@vm2 postgresql-9.2.4]#2.2、初始化数据库 useradd -d /opt/postgres postgres ######如果没有此账户就创建，前面yum安装的时候已经替我们创建了 [root@vm2 postgresql-9.2.4]# mkdir /usr/local/pgsql/data [root@vm2 postgresql-9.2.4]# chown postgres.postgres /usr/local/pgsql/data/ [root@vm2 postgresql-9.2.4]# su - postgres
-bash-4.1$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/ 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 locale "zh_CN.UTF-8". The default database encoding has accordingly been set to "UTF8". initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8" The default text search configuration will be set to "simple".
fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... 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
WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
-bash-4.1$ psql psql （9.2.4） Type "help" for help.
postgres=# create database testdb; CREATE DATABASE postgres=# c testdb; You are now connected to database "testdb" as user "postgres". testdb=# create table test（id int,name text,age int）; CREATE TABLE testdb=# d test Table "public.test" Column | Type | Modifiers --------+---------+----------- id | integer | name | text | age | integer |
testdb=# insert into test values（1,"lansgg",25）; INSERT 0 1 testdb=# select * from test; id | name | age ----+--------+----- 1 | lansgg | 25 （1 row）
易网时代-编程资源站