分类 PostgreSQL,MySQL,Nginx,PHP 下的文章

  • database, public,schema1 为你需要修改的库名
  • username 为更改后的所有者用户名
\c database;

DO $$
DECLARE
    r record;
    i int;
    v_schema text[] := '{public,schema1}';
    v_new_owner varchar := 'username';
BEGIN
    FOR r IN
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner
    LOOP
        EXECUTE r.a;
    END LOOP;
    FOR i IN array_lower(v_schema, 1)..array_upper(v_schema, 1)
    LOOP
        EXECUTE 'ALTER SCHEMA "' || v_schema[i] || '" OWNER TO ' || v_new_owner;
    END LOOP;
END
$$;

百度云加速的ip段非常之多,官方给了一个帖子来列出这些ip,获取起来十分不便。
倍感痛苦的我,最终还是决定写个PHP脚本自动更新Nginx的real_ip规则。其他规则也可以参考修改。

<?php
$bdip = read_ip_list('https://ticket-baidu.kf5.com/posts/view/148628');
$cfip = read_ip_list('https://www.cloudflare.com/ips-v4');
$list = array_merge($bdip, $cfip);

make_nginx_real_ip_conf($list);

///////////////////////////////////////////////////////////

function make_nginx_real_ip_conf($list) {
    foreach($list as &$ip) {
        $ip = "set_real_ip_from {$ip};";
    }
    $text = implode("\n", $list);
    file_put_contents('nginx_real_ip.conf', $text);
}

function read_ip_list($site) {
    $html = file_get_contents($site);
    if(!preg_match_all('/\d+\.\d+\.\d+\.\d+\/\d+/', $html, $list)) {
        exit("读取远程数据失败: {$site}\n");
    }
    return sort_ip_list($list[0]);
}

function sort_ip_list($list) {
    $rets = array();
    foreach(array_unique($list) as $val) {
        $ip = ip2long(explode('/', $val)[0]);
        $ip = sprintf('%u', floatval($ip));
        $rets[$ip] = $val;
    }
    ksort($rets);
    return array_values($rets);
}

在编译Nginx时,记得增加http_image_filter模块,然后在server区域加入如下代码即可。
如果你懒得编译这个模块,也可以下载我编译好的Webox直接安装。

#生成缩略图(等比例)
#http://www.anrip.com/expamle/test.jpg?r128x128
if ($args ~ ^r\d+x\d+$) {
    rewrite ^(.+)$ $1/$args? last;
}
location ~* \.(gif|png|jpg|jpeg)/r(\d+)x(\d+)$ {
    rewrite ^(.+)/r(\d+)x(\d+)$ $1 break;
    image_filter resize  $2 $3;
    image_filter_buffer  10M;
}
#生成裁切图片(固定高宽)
#http://www.anrip.com/expamle/test.jpg?c128x128
if ($args ~ ^c\d+x\d+$) {
    rewrite ^(.+)$ $1/$args? last;
}
location ~* \.(gif|png|jpg|jpeg)/c(\d+)x(\d+)$ {
    rewrite ^(.+)/c(\d+)x(\d+)$ $1 break;
    image_filter crop  $2 $3;
    image_filter_buffer  10M;
}

一、安装必备的软件包

sudo locale-gen zh_CN
sudo locale-gen zh_CN.UTF-8
sudo dpkg-reconfigure locales
sudo perl -MCPAN -e 'install HTML::Template'
sudo apt-get install nginx fcgiwrap rcs

二、配置Nginx站点

server {

    listen       80;
    server_name  wiki.anrip.com;

    root /srv/webroot/wiki/;
    index index.html;

    rewrite ^/$          /bin/view;
    rewrite ^/([A-Z].*)  /bin/view/$1;
    rewrite ^/edit/(.*)  /bin/edit/$1;

    location / {
        deny all;
    }

    location ~ ^/pub/ {
        allow all;
    }

    location ~ ^/bin/ {
        allow all;
        fastcgi_pass   unix:/var/run/fcgiwrap.socket;
        fastcgi_split_path_info  ^(/bin/[^/]+)(/.*)$;
        include        fastcgi_params;
        fastcgi_param  PATH_INFO        $fastcgi_path_info;
        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
    }

}

三、配置TWiki站点

http://wiki.anrip.com/bin/configure
此处比较重要,可能会提示一些缺少的软件包等,务必要解决;
另外,语言等也是在此处设置,无需像网上所讲的需要从源代码修改配置。