在本文中，我们将详细介绍PostgreSQL中的AlterTable太慢了的各个方面，同时，我们也将为您带来关于ALTERTABLE,在nullnull列中设置null,PostgreSQL9.1、c
在本文中，我们将详细介绍PostgreSQL中的Alter Table太慢了的各个方面，同时，我们也将为您带来关于ALTER TABLE,在null null列中设置null,PostgreSQL 9.1、centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、Portable PostgreSQL、PostgreSQL 14 ALTER TABLE DETACH 支持 CONCURRENTLY的有用知识。
本文目录一览：- PostgreSQL中的Alter Table太慢了
- ALTER TABLE,在null null列中设置null,PostgreSQL 9.1
- centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教
- Portable PostgreSQL
- PostgreSQL 14 ALTER TABLE DETACH 支持 CONCURRENTLY
PostgreSQL中的Alter Table太慢了
我正在尝试添加新列
ALTER TABLE "Cidade" ADD COLUMN "BoundBox" VARCHAR(255)到此表：
"Cidade" "Id" integer not null constraint "Cidade_PK" primary key, "Nome" varchar(120), "EstadoId" integer not null constraint "Estado_Cidade_FK" references "Estado", "PontoCentralLatitude" numeric, "PontoCentralLongitude" numeric但是查询从未完成，我已经等了5分钟，什么也没发生。该表只有5,000条记录，由于它阻止了对该表的访问，我迫不及待地等待了太多时间。我有一个测试数据库（等于生产版本），并且工作非常迅速。postgres版本是9.5.6。
答案1
小编典典如果您运行的是PostgreSQL 9.6+，则可以pg_blocking_pids()用来查找锁定您的查询的PID。
select pid, pg_blocking_pids(pid) as blocked_by, query as blocked_queryfrom pg_stat_activitywhere pg_blocking_pids(pid)::text != ''{}'';
ALTER TABLE,在null null列中设置null,PostgreSQL 9.1
我的意思是，我想做这样的事情：
postgres=# ALTER TABLE person ALTER COLUMN phone SET NULL;
但它显示：
postgres=# ALTER TABLE person ALTER COLUMN phone SET NULL; ERROR: Syntax error at or near "NULL" LINE 1: ALTER TABLE person ALTER COLUMN phone SET NULL;
ALTER TABLE person ALTER COLUMN phone DROP NOT NULL;
更多细节在手册：http://www.postgresql.org/docs/9.1/static/sql-altertable.html
centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教
1. 下载源码
$ mkdir /usr/downloads $ wget -c http://cn2.php.net/distributions/php-5.6.20.tar.gz $ tar -xvf php-5.6.20.tar.gz $ mv php-5.6.20 /usr/local/src $ cd !$ & cd php-5.6.20
2. 阅读安装指导
$ ls -also $ less README $ less INSTALL
3. 安装依赖包
$ yum install apr apr-util apr-devel apr-util-devel prce lynx
4. 安装httpd
$ wget -c http://apache.fayea.com//httpd/httpd-2.4.20.tar.gz $ tar -xvf httpd-2.4.20.tar.gz $ cd httpd-2.4.20 $ ./configure \ --prefix=/usr/local/programs/apache2 \ --enable-rewrite \ --enable-so \ --enable-headers \ --enable-expires \ --with-mpm=worker \ --enable-modules=most \ --enable-deflate \ --enable-module=shared $ make $ make install $ cd /usr/local/programs/apache2 $ cp bin/apachectl /etc/init.d/httpd ## 复制启动脚本 $ /etc/init.d/httpd start ## 启动apache服务器,访问http://localhost/ $ egrep -v ''^[ ]*#|^$'' /usr/local/apache2/conf/httpd.conf | nl ## 查看apache服务器的配置 ## 将apache加入系统服务 vi /etc/rc.d/rc.local ``` /usr/local/programs/apache2/bin/apachectl start ``` $ cat /etc/rc.local
4. 安装postgresql
立即学习“PHP免费学习笔记（深入）”；
$ yum install readline-devel ## 安装readline依赖 $ cd /usr/downloads $ wget -c https://ftp.postgresql.org/pub/source/v9.5.0/postgresql-9.5.0.tar.bz2 $ tar -xvf postgresql-9.5.0.tar.bz2 $ cd postgresql-9.5.0 $ ./configure --prefix=/usr/local/programs/postgresql $ make $ su $ make install $ /sbin/ldconfig /usr/local/programs/postgresql/lib ## 刷新下共享动态库 $ cd /usr/local/programs/postgresql $ bin/psql --version ## 检查运行情况 ## 开始对postgresql的配置 $ vi /etc/profile.d/postgresql.sh ## 增加环境变量,不推荐直接在/etc/profile中添加,系统更新升级时会需要merge ``` PATH=/usr/local/programs/postgresql:$PATH export PATH ``` $ source /etc/profile ## 更新环境变量 ## 增加用户和其他文件夹 $ adduser postgres $ passwd postgres $ mkdir /usr/local/programs/postgresql/logs $ mkdir /usr/local/programs/postgresql/data $ chown postgres /usr/local/programs/postgresql/data $ su - postgres ## 初始化数据库 $ ./bin/initdb -D ./data $ ./bin/createdb test $ ./bin/psql test ## 已有数据库，可导入data文件夹后尝试root访问，假如带密码，可能需要进一步研究下 $ ./bin/postgres -D ./data >./logs/start-log-1.log 2>&1 & $ ./bin/psql --list ##列出数据库 ## ok，安装完成 ## 自定义设置，权限控制等，可以跳过，等熟悉使用后再做 ## 编辑数据库配置及权限文件： $ vi /usr/local/programs/postgresql/data/postgresql.conf ## 数据库配置文件 $ chown postgres postgresql.conf $ chmod 644 postgresql.conf $ vi /usr/local/programs/postgresql/data/pg_hba.conf ## 权限文件 $ vi /usr/local/programs/postgresql/data/pg_ident.conf ## 设置开机自启动： $ vi /etc/rc.d/rc.local ## 添加如下内容 ``` /usr/local/programs/postgresql/bin/postgresql start ```
5. 安装php
## 源码已经在第一步中下载，现在开始安装:
$ yum install libxml2 libxml2-devel libpng libpng-devel libjpeg libjpeg-devel freetype freetype-devel
$ ./configure \
--prefix=/usr/local/programs/php \
--with-apxs2=/usr/local/programs/apache2/bin/apxs \
--with-zlib \
--with-gd \
--with-jpeg-dir \
--with-png-dir \
--with-freetype-dir \
--with-zlib-dir \
--enable-mbstring \
--with-pgsql=/usr/local/programs/postgresql \
--with-pdo-pgsql=/usr/local/programs/postgresql
$ make
$ make test
> Bug #42718 (unsafe_raw filter not applied when configured as default filter) [ext/filter/tests/bug42718.phpt] XFAIL REASON: FILTER_UNSAFE_RAW not applied when configured as default filter, even with flags
> Bug #67296 (filter_input doesn''t validate variables) [ext/filter/tests/bug49184.phpt] XFAIL REASON: See Bug #49184
> Bug #53640 (XBM images require width to be multiple of 8) [ext/gd/tests/bug53640.phpt] XFAIL REASON: Padding is not implemented yet
> zend multibyte (7) [ext/mbstring/tests/zend_multibyte-07.phpt] XFAIL REASON: https://bugs.php.net/bug.php?id=66582
> zend multibyte (9) [ext/mbstring/tests/zend_multibyte-09.phpt] XFAIL REASON: https://bugs.php.net/bug.php?id=66582
>Bug #70470 (Built-in server truncates headers spanning over TCP packets) [sapi/cli/tests/bug70470.phpt] XFAIL REASON: bug is not fixed yet
## 查阅官方的bug，发现：
> id=66582: status : Closed. Fixed in master (PHP7)
> id=42718: status : Assigned
> id=42718: reference to id=49184, unsolved for many years
## 那就不关心了，直接装吧
$ make install
> You may want to add: /usr/local/programs/php/lib/php to your php.ini include_path
## 那就按它说的设置吧
$ cp php.ini-development /usr/local/programs/php/lib/php.ini
```
include_path = ".;/usr/local/programs/php/lib/php"
## 然后，编辑httpd的设置，确保其能正确解析php文件
```
...
LoadModule php5_module modules/libphp5.so
...
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .php5
...
<ifmodule dir_module>
DirectoryIndex index.html index.php
</ifmodule>
```
## 重启httpd，测试
$ cd /usr/local/programs/apache2
$ bin/httpd -h
$ bin/httpd -k stop
$ bin/httpd -f conf/httpd.conf
## 默认设置的www页面在./htdocs/下，那就先去里面建一个测试页面吧
$ vi htdocs/index.php
```
<?php phpinfo(); ?>
```
$ curl http://localhost/index.php |grep postgresql
#ok
后续应该做的事
* 1. 启动时，不需要要手动指定配置文件
* 2. php初始化www目录设置
* 3. php 用户、权限管理等
以上就介绍了centos 7下源码编译安装php支持PostgreSQL，包括了postgresql,centos 7方面的内容，希望对PHP教程有兴趣的朋友有所帮助。
Portable PostgreSQL
Portable PostgreSQL
介绍
PostgreSQL Portable
PostgreSQLPortable is an open source database packaged as a portable app, so you can run a full PostgreSQL database on your iPod, USB flash drive, portable hard drive, etc. It has all the same features as PostgreSQL, plus, it leaves no personal information behind on the machine you run it on, so you can take it with you wherever you go.
PostgreSQLPortable 是一个开源的便携数据库APP，你可以运行一个完整的PostgreSQL数据库，这个数据库存放在你的ipod,U盘等移动存储设备，它拥有与PostgreSQL医院的特性。它不会存留任何个人的信息在你的运行它的机器上，你可以用移动存储设备随时带走。
Features
- No installation required. Simply download, extract and run! 无需安装，下周解压立即使用。
- Runs a complete PostgreSQL Server instance.运行一个完全的PostgreSQL实例；
- Does not require a local "postgres" user account or administrator privileges, works for any user.不需要建立一个本地的 "postgres" 用户或者超级用户权限，可以用任何用户运行。
- Completely portable - runs off a USB, cloud drive (DropBox, iCloud drive, OneDrive, etc) or hard drive.完全便携，运行在USB存储设备，云盘或者硬盘上。
- Packaged in PortableApps.com format for easy integration. PortableApps.com 方便集成。
System Requirements
- Microsoft Windows 8, 7, Vista or XP.
- PortableApps.com Platform (Optional)
下载
https://sourceforge.net/projects/postgresqlportable/files/?source=navbar
https://github.com/garethflowers/postgresql-portable
使用
下载下来，解压到一个目录下，这个目录路径不能有中文。。。
运行:
PostgreSQLPortable.exe
查看角色
postgresql数据库用户
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | …
查看用户
postgres=# SELECT u.usename AS "User name",
u.usesysid AS "User ID",
CASE WHEN u.usesuper AND u.usecreatedb THEN CAST(''superuser, create
database'' AS pg_catalog.text)
WHEN u.usesuper THEN CAST(''superuser'' AS pg_catalog.text)
WHEN u.usecreatedb THEN CAST(''create database'' AS
pg_catalog.text)
ELSE CAST('''' AS pg_catalog.text)
END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
用户名：postgres
客户端
HeidiSQL
http://www.heidisql.com/
用户是：postgres
端口是:5432
pgAdmin
https://www.pgadmin.org/
建议pgAdmin III
教程
http://www.yiibai.com/html/postgresql/2013/080116.html
PostgreSQL 14 ALTER TABLE DETACH 支持 CONCURRENTLY
担心ALTER TABLE DETACH因忘记设置statement_timeout而长时间锁表吗？ PostgreSQL 14支持CONCURRENTLY了， 完美解决烦恼。
mydb=#
mydb=# create table test_list(id serial, flag text, location text, create_date date) partition by list(flag); /*创建测试表*/
CREATE TABLE
mydb=#
mydb=# do --利用匿名块快速创建多个list分区子表
mydb-# $$
mydb$# declare base text; sqlstring text; i int;
mydb$# begin
mydb$# base = ''create table test_list_%s partition of test_list for values in (''''%s'''')'';
mydb$# for i in 0..9 loop
mydb$# sqlstring = format(base, ''flag'' || i, ''flag'' || i);
mydb$# --raise notice ''%'', sqlstring;
mydb$# execute sqlstring;
mydb$# end loop;
mydb$# end
mydb$# $$language plpgsql;
DO
mydb=#
mydb=# \d+ test_list
Partitioned table ''public.test_list''
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+---------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval(''test_list_id_seq''::regclass) | plain | | |
flag | text | | | | extended | pglz | |
location | text | | | | extended | pglz | |
create_date | date | | | | plain | | |
Partition key: LIST (flag)
Partitions: test_list_flag0 FOR VALUES IN (''flag0''),
test_list_flag1 FOR VALUES IN (''flag1''),
test_list_flag2 FOR VALUES IN (''flag2''),
test_list_flag3 FOR VALUES IN (''flag3''),
test_list_flag4 FOR VALUES IN (''flag4''),
test_list_flag5 FOR VALUES IN (''flag5''),
test_list_flag6 FOR VALUES IN (''flag6''),
test_list_flag7 FOR VALUES IN (''flag7''),
test_list_flag8 FOR VALUES IN (''flag8''),
test_list_flag9 FOR VALUES IN (''flag9'')
mydb=# alter table test_list detach partition test_list_flag0 concurrently ;
ALTER TABLE
mydb=#
mydb=# alter table test_list detach partition test_list_flag3 concurrently; /*被另一时事物阻塞 然后取消执行*/
^CCancel request sent
ERROR: canceling statement due to user request
mydb=# \d+ test_list
Partitioned table ''public.test_list''
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+---------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval(''test_list_id_seq''::regclass) | plain | | |
flag | text | | | | extended | | |
location | text | | | | extended | | |
create_time | timestamp with time zone | | | | plain | | |
Partition key: LIST (flag)
Partitions: test_list_flag1 FOR VALUES IN (''flag1''),
test_list_flag2 FOR VALUES IN (''flag2''),
test_list_flag3 FOR VALUES IN (''flag3'') (DETACH PENDING),
test_list_flag4 FOR VALUES IN (''flag4''),
test_list_flag5 FOR VALUES IN (''flag5''),
test_list_flag6 FOR VALUES IN (''flag6''),
test_list_flag7 FOR VALUES IN (''flag7''),
test_list_flag8 FOR VALUES IN (''flag8''),
test_list_flag9 FOR VALUES IN (''flag9'')当CONCURRENTLY被取消后，detach的分区表会显示DETACH PENDING， 需要使用ALTER TABLE … DETACH PARTITION … FINALIZE去完成被取消的DETACH， 才能去detach其它分区。
mydb=# alter table test_list detach partition test_list_flag3 concurrently;
ERROR: partition ''test_list_flag3'' already pending detach in partitioned table ''public.test_list''
HINT: Use ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete the detach operation.
mydb=#
mydb=# alter table test_list detach partition test_list_flag2 concurrently;
ERROR: partition ''test_list_flag3'' already pending detach in partitioned table ''public.test_list''
HINT: Use ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete the detach operation.
mydb=#
mydb=# alter table test_list detach partition test_list_flag3 finalize;
ALTER TABLE
mydb=#
mydb=# \d+ test_list
Partitioned table ''public.test_list''
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+---------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval(''test_list_id_seq''::regclass) | plain | | |
flag | text | | | | extended | | |
location | text | | | | extended | | |
create_time | timestamp with time zone | | | | plain | | |
Partition key: LIST (flag)
Partitions: test_list_flag1 FOR VALUES IN (''flag1''),
test_list_flag2 FOR VALUES IN (''flag2''),
test_list_flag4 FOR VALUES IN (''flag4''),
test_list_flag5 FOR VALUES IN (''flag5''),
test_list_flag6 FOR VALUES IN (''flag6''),
test_list_flag7 FOR VALUES IN (''flag7''),
test_list_flag8 FOR VALUES IN (''flag8''),
test_list_flag9 FOR VALUES IN (''flag9'')
mydb=#
今天关于PostgreSQL中的Alter Table太慢了的讲解已经结束，谢谢您的阅读，如果想了解更多关于ALTER TABLE,在null null列中设置null,PostgreSQL 9.1、centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、Portable PostgreSQL、PostgreSQL 14 ALTER TABLE DETACH 支持 CONCURRENTLY的相关知识，请在本站搜索。
本文标签：
