我们假设有VLAN A和B，需要做到A可以访问B，而B不可以访问A
A的地址段为 X.X.X.X/24
B的地址段为 Y.Y.Y.Y/24

对于单向流量的控制，一般来说是属于防火墙的功能。但实际上，交换机也是可以通过ACL的灵活配置，来实现最基本的单向流量控制。具体配置如下：

对于ICMP协议，需要允许B->A的icmp echo-reply报文：

1
2

acl 3001
   rule 5 permit icmp source Y.Y.Y.Y 0.0.0.255 destination X.X.X.X 0.0.0.255  icmp-type echo-reply

对于TCP协议，需要允许B->A，报文头中SYN Flag的类型为ack(010000)或rst(000100)的TCP报文：
———-V2R2版本及以前的配置————

1
2

rule 9 permit tcp source Y.Y.Y.Y 0.0.0.255 destination X.X.X.X 0.0.0.255 tcp-flag ack
rule 10 permit tcp source Y.Y.Y.Y 0.0.0.255 destination X.X.X.X 0.0.0.255 tcp-flag rst

———-V2R3版本及以后的配置————

1

rule 10 permit tcp source Y.Y.Y.Y 0.0.0.255 destination X.X.X.X 0.0.0.255 tcp-flag established

对于UDP协议，由于其为无状态的特性，无法实现单通，基于业务优先原则，放开所有UDP协议文：

1

rule 15 permit udp source Y.Y.Y.Y 0.0.0.255 destination X.X.X.X 0.0.0.255

最后，deny其他B->A的IP流量，并基于业务优先原则，放开其他所有未知流量：

1
2

rule 20 deny ip source Y.Y.Y.Y 0.0.0.255 destination X.X.X.X 0.0.0.255
rule 25 permit ip

将该ACL通过traffic-filter应用于VLAN之上

1

traffic-filter vlan B inbound acl 3001

postgresql 中join的选项

• Nested Loops(O(n2))

  1 2 3 4 5 6

  for x in table1: for y in table2: if x.field == y.field issue row else keep doing

• Hash joins

  1 2 3

  Hash join Sequentialscan table 1 Sequentialscan table 2

• Merge joins(O(n * log(n)))

  1 2 3 4 5 6 7 8 9

  Merge join Sort table 1 Sequetial scan table 1 Sort table 2 Sequetial scan table 2 --- Merge join Index scan table 1 Index scan table 2

参考

0. explain 计划

   1	explain (analyze ,verbose ,consts ,timing ,buffers) Select ...

1. pg_stat_activity(观察当前系统活动),每个连接一行记录.

   1 2	\d pg_stat_activity Select pid ,query_stat ,state_change ,state ,query From pg_stat_activity;

   • pg_cancel_backend. (Select pg_cancel_backend(pid)),终止pid,保留连接.
   • pg_terminate_backend.

2. 检查数据库,pg_state_database

   1	\d pg_state_database

3. 检查表 pg_stat_user_tables ,pg_statio_user_tables

   1 2 3 4 5 6 7 8

   \d pg_stat_user_tables Select schemaname, relname ,seq_scan ,seq_tup_read, seq_tup_read /seq_scan as avg, idx_scan From pg_stat_user_tables Where seq_scan > 0 Order By seq_tup_read Desc Limit 25; \d pg_statio_user_tables

4. 深入索引 pg_stat_user_indexes

   1 2 3 4 5 6 7

   \d pg_stat_user_indexes Select schemaname ,relname ,indexrelname ,idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as idx_size, pg_size_pretty(sum(pg_relation_size(indexrelid)) Over (Order By idx_scan ,indexrelid)) as total From pg_stat_user_indexes Order By 6;

5. 使用 pg_stat_statements

   1. postgresql.conf 中将 pg_stat_statements 加入 shared_preload_libraties.
   2. 重启 db server.
   3. 在你的database中运行 CREATE EXTESION pg_stat_statements.

      1 2 3 4 5 6 7 8 9 10 11

      \d pg_stat_statements Select round((100 * total_time /sum(total_time) Over ())::numeric, 2) percent, round(total_time::numeric ,2) as total, calls, round(mean_time::numeric ,2) as mean, substring(query, 1, 40) From pg_stat_statements Order By total_time DESC Limit 10; Select pg_stat_statements_reset();

显示文件位置的命令

1
2
3
4

SHOW data_directory;
SHOW config_file;
Select name ,setting From pg_settings Where category = 'File Locations';
SHOW all;

• command-line reload

  1 2 3 4 5 6 7

  #方法1 sudo service postgresql-x.x reload #方法2 su -c root 'service postgresql-x.x reload' #方法3 su - postges /usr/bin/pg_ctl reload

• using sql reload

  1	Select pg_reload_conf();

转载

• index scan 扫描索引,查找后立即读取对应数据页,是随机读.

• bitmap heap scan 最终是顺序读. 一般用在多个索引同时使用时或单个索引多次使用时.

举例(先bitmap index scan 再bitmap heap scan):

bitmaps 按为与

bitmap heap scan 读入对应的页,再次按指定条件查找记录(re-checked).
.

postgresql 架构命令

1
2
3
4
5
6
7
8
9

Select current_schema();
Show search_path;
Create Schema ...;
Alter Schema ...;
Drop Schema ...;
Set search_path To ..., ...
Grant Usage On Schema schema_name To user_name
Grant Create On Schema schema_name To user_name;
\dn #get the list of schemas;

移动PostgreSQL数据目录

转载

• 移动数据目录

  1 2 3 4 5

  $ sudo -u postgres psql postgres# SHOW data_directory $ sudo systemctl stop postgresql $ sudo systemctl status postgresql # 确认已停止. $ sudo rsync -av /var/lib/postgresql /mnt/... # -a 保留权限

• 指向新数据库

  1	$ sudo vim /etc/postgresql/.../postgresql.conf # 修改: data_directory = '新路径...'

• 重启PostgreSQL,并验证

  1 2 3 4

  $ sudo systemctl start postgresql $ sudo systemctl status postgresql $ sudo -u postgresql psql postgres=# SHOW data_directory

PostgreSQL 客户端常用的连接参数
注意:密码是不可能写在命令行上的,设置密码用.pgpass,环境变量

1
2
3
4
5

Connection options:
    -d, --dbname=DBNAME 
    -h, --host=HOSTNAME
    -p, --port=PORT
    -U, --username=NAME connect as specified database user

使用环境变量 连接PostgreSQL

• PGHOST
• PGPORT
• PGUSER
• PGPASSWORD
• PGDATABASE

使用.pgpass 连接PostgreSQL
在你的HOME目录下创建.pgpass文件,字段可用*
格式: hostname:port:database:username:password

1

chmod 0600 ~/.pgpass

Shells can have variables of 2 types: locals, which are only accessible from the current shell, and (exported) environment variables, which are passed on to every executed program. set can see shell-local variables, env cannot.

Since set is a built-in shell command, it also sees sees shell-local variables (including shell functions). env on the other hand is an independent executable; it only sees the variables that the shell passes to it, or environment variables.

When you type a line like a=1 then a local variable is created (unless it already existed in the environment). Environment variables are created with export a=1

What we have here the difference between environment variables and shell variables. Under sh (and variants) all environment variables are automatically shell variables as well, but the same is not true in the other direction (this originally was to save memory (I think)).

Environment variables are stored in an area of memory that is kept when a process calls exec(), this means that programs you run from the command line get a copy of the environment. Shell variables do not stay over an exec(), but are copied in a fork(). When you create a subshell the shell calls fork() in order to copy the present process, but it does NOT call exec() because the process it requires is already there thus the shell variables are kept.

Also most shells set for you certain shell variables when you start them up, PS1 is a perfect example of this, and you may have also set them yourself in your shell initalisation file.

In summary, shell variables are used by the shell and copies it makes of itself, while environment variables are used by everything.