PostgreSQL使用记录

PostgreSQL新手入门http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html

windows 2003安装问题:
1. problem running post install setup
安装路径权限问题,配置Users的权限为完全控制,就可以了.

两个配置文件:
sudo gedit /var/lib/pgsql/9.3/data/postgresql.conf
sudo gedit /var/lib/pgsql/9.3/data/pg_hba.conf

启动:sudo systemctl restart postgresql-9.3.service

pgAdmin 使用总结及postgreSQL常用操作 http://blog.sina.com.cn/s/blog_6d2bb4da0100xh6n.html

登录数据库:

1	psql -U ambari -d ambari -h 127.0.0.1 -p 5432

创建表空间:你创建表空间的用户是postgres,那么，在修改此目录的控制权限的时候
sudo mkdir /var/tablespace
sudo chown -R postgres:postgres /var/tablespace
su postgres
psql test;
CREATE TABLESPACE tablespacename OWNER postgres LOCATION ‘/var/tablespace’;

创建数据库
su postgres 登陆用户,如果不存在可以创建
createdb test 创建数据库
psql test 切换到test数据库
CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD ‘test’; 创建角色
CREATE USER kanon PASSWORD ‘kanon2′; 创建账户 CREATE USER除了默认具有LOGIN权限之外，其他与CREATE ROLE是完全相同的。
psql -h localhost -U testuser test 测试账户
使用模板template0创建数据库两种方法
（1）createdb -T template0 dbname
（2）create database dbname template=template0
CREATE DATABASE aaaa WITH ENCODING=’UTF8’ OWNER=testuser TEMPLATE=template0 CONNECTION LIMIT=10 TABLESPACE=tablespacename;
创建数据库是指定表空间:
CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;

备份恢复工具
Barman 1.4.0 发布，PostgreSQL 备份和恢复管理器 http://www.oschina.net/news/59146/barman-1-4-0
备份和恢复PostgreSQL数据库 http://publish.it168.com/2005/0825/20050825099001.shtml
PostGresql 备份和恢复 http://zwb8686.blog.51cto.com/982393/1140172
PgSql备份pg_dump与还原手记pg_restorehttp://www.cnblogs.com/wangbin/archive/2009/08/14/1546009.html

//////备份:
pg_dump -h localhost -p 5432 -U testuser test>pandy_test.bak
pg_dump -h 192.168.0.198 -p 5432 -b -c -C -Fc -U postgres rhcpm_dev4>/home/pandy/rhcpm_dev4.backup //-Fc之间有没有空格，　好像都一样．
/////恢复:
I:
pg_restore -h 192.168.2.102 -p 5432 -U postgres -W -d rhcpm_dev4 -v /home/pandy/桌面/rhcpm_dev4-2015-06-19_172242.backup
II:
\set ON_ERROR_STOP
psql -h localhost -p 5432 -U testuser test<pandy_test.bak

数据从服务器复制到本地数据库
自己些的代码
export date_str=$(date “+%Y-%m-%d_%H%M%S”)
echo $date_str
pg_dump -h 192.168.0.167 -p 5432 -b -c -C -Fc -U postgres rhcpm_dev4>/home/pandy/桌面/rhcpm_dev4-“$date_str”.backup;
pg_restore -h 192.168.2.102 -p 5432 –if-exists -c -U postgres -W -d rhcpm_dev4 -v /home/pandy/桌面/rhcpm_dev4-“$date_str”.backup;

pgadminIII工具看到的代码:
export date_str=$(date “+%Y-%m-%d_%H%M%S”)
echo $date_str
pg_dump –host 192.168.0.5 –port 5432 –username “postgres” –role “postgres” –no-password –format custom –blobs –verbose –file “/home/pandy/桌面/rhcpm_saas-$date_str.backup” “rhcpm_saas”
pg_restore –host 192.168.0.5 –port 5432 –username “postgres” –dbname “rhcpm_saas_test” –role “postgres” –no-password –verbose “/home/pandy/桌面/rhcpm_saas-$date_str.backup”

自动备份
Windows http://www.oschina.net/question/100267_71299
PostgreSQL自动备份的批处理文件 http://blog.csdn.net/adeng1919/article/details/13022859

Java代码

@ECHO OFF

echo 正在初始化环境变量. . .

echo.

:: 对于路径中有空格的路径字符串，需要加上英文双引号包裹！否则就会出现命令错误！

set POSTGRESQL_DIR="C:\PostgreSQL\9.3\bin"

set DB_NAME=rhcpm_dev4

set USER="postgres"

:: 数据库管理系统名

set DBMS_NAME=PostgreSQL

set SV_IP="192.168.0.167"

set SV_PORT="5432"

set DBBAK_DIR="C:\PostgreSQL\pg_db_bak"

set CMD_STR="-b -c -C -Ft -U"

:: PostgreSQL的pg_dump命令行工具没有带密码的参数，但是可以设置一个PGPASSWORD的环境变量来提供密码

set PGPASSWORD=1

echo 正在备份数据库. . .

echo.

:: 以下是获得当前系统时间的命令，e.g. 20120503101305

:: 年

set myyy=%date:~0,4%

:: 月

set mymm=%date:~5,2%

:: 日

set mydd=%date:~8,2%

::echo %myyy% %mymm% %mydd%

set /a TODAY=%date:~0,4%%date:~5,2%%date:~8,2%

set _TIME=%time:~0,8%

::echo %_TIME%

set CURRENTTIME=%_time::=%

set CURRENTTIME=%CURRENTTIME: =0%

::set MYDATETIME=%TODAY%-%CURRENTTIME%

::set MYDATETIME=%TODAY%-%_TIME%

::set MYDATETIME=%myyy%-%mymm%-%mydd%_%CURRENTTIME%

set MYDATETIME=%TODAY%_%CURRENTTIME%

cd %POSTGRESQL_DIR%

:: 使用PostgreSQL提供的pg_dump命令将具体数据库导出为.sql文件

%POSTGRESQL_DIR%\pg_dump.exe -h %SV_IP% -p %SV_PORT% -b -c -C -Ft -U %USER% %DB_NAME%>%DBBAK_DIR%\%DB_NAME%_%MYDATETIME%.backup

echo 备份结束

echo

exit

PostgreSQL自动备份(backup)与恢复(restore)数据库(图解)https://xly3000.wordpress.com/2012/03/07/postgresql%E8%87%AA%E5%8A%A8%E5%A4%87%E4%BB%BDbackup%E4%B8%8E%E6%81%A2%E5%A4%8Drestore%E6%95%B0%E6%8D%AE%E5%BA%93%E5%9B%BE%E8%A7%A3/
Linux http://mlm.iteye.com/blog/1129709

序列
postgresql 创建序列方法_postgresql create sequence http://www.jsjtt.com/shujuku/postgresql/32.html
PostgreSQL 序列（SEQUENCE） http://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html
PostgreSQL 8.1 序列操作函数 http://www.php100.com/manual/PostgreSQL8/functions-sequence.html
— DROP SEQUENCE seq_user_id;
CREATE SEQUENCE seq_user_id INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
ALTER TABLE seq_route_id OWNER TO postgres;

查找所有数据库
SELECT datname FROM pg_database;

查找所有用户表
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ‘pg%’ AND tablename NOT LIKE ‘sql_%’ ORDER BY tablename;

查找表的所有字段

1 2	Sql代码 SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where c.relname='rh_dimension' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid

postgres查询表中所有字段、主键、唯一、外键、是否为空

Sql代码

select 'true' as list,'true' as edit,'false' as search,a.attname as column_name,format_type(a.atttypid,a.atttypmod) as data_type,

(case

when atttypmod-4>0 then atttypmod-4

else 0

end)data_length,

(case

when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y'

else 'N'

end) as P,

(case

when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y'

else 'N'

end) as U,

(case

when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y'

else 'N'

end) as R,

(case

when a.attnotnull=true then 'Y'

else 'N'

end) as nullable,

col_description(a.attrelid,a.attnum) as comment,'XEditText' as control

from pg_attribute a

where attstattarget=-1 and attrelid = (select oid from pg_class where relname ='userinfo')

日期格式化
SELECT to_char(CURRENT_DATE,’yyyy-MM-dd hh24:MI:ss’)

查找时间
SELECT current_date,current_time

当前时间
SELECT now()

插入时间
INSERT INTO _sequence_table(create_date) VALUES (DATE ‘2001-02-16’);

时间日期
SELECT TIMESTAMP ‘2001-02-16 20:38:40’;

创建日期时间类型和保存方式
create_datetime timestamp without time zone,
to_timestamp(‘2013-05-20 18:29:42′,’yyyy-mm-dd hh24:mi:ss’)

替换
replace(tablename, ‘tb_’, ”)

不静之心

发表评论取消回复

访问信息

功能

近期评论

不静之心

PostgreSQL使用记录

发表评论 取消回复

访问信息

功能

近期评论

发表评论取消回复