博客

postgresql内存表主要使用操作系统实现,自身不带有这个功能.使用内存表的前提是内存必须足够大,同时要测算好内存表中的数据大小,注意(postgresql使用的内存大小 + 内存挂载成硬盘占用的内存大小 + 操作系统使用的内存大小 + 其它软件使用的大小) < 物理内存大小.具体方法如下(以256GB物理内存,专用postgresql服务器为例):

postgresql内存配置估算为156GB( 134GB + 其它预留 )

        shared_buffers = 128GB
        work_mem=32MB
        max_connections = 100
        temp_buffers=2097151KB
        maintenance_work_mem=2097151KB
        max_connections*work_mem+shared_buffers+temp_buffers+maintenance_work_mem 约等于  ( (100*32/1024) + 128 +  (2097151/1024/1024) * 2 )  = 134GB

操作系统内存估算预留12GB

剩余内存估算为(88GB)

        256 - 156 - 12 = 88GB

1.将内存挂载成硬盘

Windows下把内存挂载成硬盘可以使用第三方内存虚拟硬盘软件

Linux:

创建目录

        sudo mkdir /pgRAM

临时挂载

        sudo mount -t tmpfs -o size=88G tmpfs /pgRAM

查看挂载情况

        sudo df -h

取消挂载

        sudo umount tmpfs /pgRAM

永久挂载

sudo vim /etc/fstab

在文件最后添加以下内容

tmpfs   /pgRAM  tmpfs  defaults,size=88G    0   0

立即生效

sudo mount -a 

现在重启计算机

创建初始化sql脚本

        su postgres
        vim /pgdata/init_RAM.sql

添加以下内容

        drop table if exists  mt_test;
        
        drop tablespace if exists pgRAM;
        
        create tablespace pgRAM owner postgres location '/pgRAM';
        
        create unlogged table mt_test(
            objectid int not null,
            name text not null,
            describe jsonb,
            constraint pk_vehicles_objectid primary key (objectid) using index tablespace pgRAM
        ) tablespace pgRAM;
        
        create index idx_test_name on mt_test(name)  tablespace pgRAM;

设置免密码登录

        su postgres
        vim /pgdata/pg_hba.conf

修改内容(你可以根据自己的需要设置免密码登录的数据库或用户),将host all all 127.0.0.1/32 md5中的md5改为trust

        host    all all 127.0.0.1/32    trust

添加postgresql RAM启动脚本

        sudo vim /etc/init.d/PGRAM

添加以下内容

#!  /bin/sh
### BEGIN INIT INFO
# Provides: PGRAM 
# Required-start:    $postgreSQL
# Required-Stop:     $postgreSQL
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: init postgresql RAM tablespace
# Description:       starts postgresql ram tablespace startup scrip
### END INIT INFO

case "$1" in
start)
    sleep 5
    chown -R postgres /pgRAM
    chmod -R 700 /pgRAM
    su - $PGUSER -c "/usr/local/postgresql/bin/psql -h 127.0.0.1 -U postgres -d postgres -f /pgdata/init_RAM.sql"
    echo "ok"
;;
stop)
    echo "ok"
;;
esac

exit 0

设置可执行权限

        sudo chmod +x /etc/init.d/PGRAM

添加postgresql RAM服务

        sudo update-rc.d PGRAM defaults start 99 stop 99

删除postgresql RAM服务

        sudo update-rc.d PGRAM remove

在添加服务过程中如出现下列警告

insserv: warning: script 'K01postgresql' missing LSB tags and overrides
insserv: warning: script 'postgresql' missing LSB tags and overrides

修改postgresql启动脚本

        sudo vim /etc/init.d/postgresql

在#!/bin/bash下面添加

### BEGIN INIT INFO
# Provides:          postgreSQL
# Required-Start:
# Required-Stop:
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: postgresql service
# Description:       postgresql service daemon
### END INIT INFO

然后移除服务重新添加即可.

2.在postgresql中手动操作(如果执行了1,此步骤省略)

在pg中创建在内存硬盘上的表空间(必须在postgres用户下操作,同时owner指定给具体的用户)

        create tablespace pgRAM owner postgres location '/pgRAM';

创建表,按正常的创建流程操作,只是在最后添加使用的表空间(在当前数据库用户下操作)

        create unlogged table mt_test(
            objectid int not null,
            name text not null,
            describe jsonb,
            constraint pk_vehicles_objectid primary key (objectid)
        ) tablespace pgRAM;
        
        create index idx_test_name on mt_test(name)  tablespace pgRAM;

3.测试

    psql -h 127.0.0.1 -U postgres
        insert into mt_test(objectid,name)
            select id,'aa' from generate_series(1,1000000) as id;
    
    select * from mt_test where objectid>900000;

4.注意事项

创建内存表时需要明确指出使用的表空间.

在内存表数据是临时的,重启postgresql没有问题,重启操作系统后数据就没有了,使用前应检查是否有数据,如果没有从正常表中把数据加载到临时表.

保存数据和正常表一样,需要注意的是内存表的数据应做update处理,正常表中的数据按平常操作.

操作和正常表完全一至.

临时表和索引占用的空间不能超过定义的大小88G.

在内存表数据是临时的,重启postgresql没有问题,重启操作系统后数据就没有了.

在内存表数据是临时的,重启postgresql没有问题,重启操作系统后数据就没有了.

重要的事情说三遍.