Using tempfs as the stats_temp_directory

  1. Create a directory for our new mount point:

    # mkdir /u01/app/pgdata/pgsql_stats_tmp

    # chmod 777 /u01/app/pgdata/pgsql_stats_tmp

    # chmod +t /u01/app/pgdata/pgsql_stats_tmp

  2. Mmount tmpfs

    # mount -t tmpfs tmpfs /u01/app/pgdata/pgsql_stats_tmp -o size=1G

    We now have a file system of 1 GB.

    # df -h /u01/app/pgdata/pgsql_stats_tmp

    Filesystem Size Used Avail Use% Mounted on
    tmpfs 1.0G 0 1.0G 0% /u01/app/pgdata/pgsql_stats_tmp

    Every time the server reboots, /u01/app/pgdata/pgsql_stats_tmp will be gone. To make the configuration persistent, add this line to /etc/fstab with an upper limit of 1GB:

    tmpfs /u01/app/pgdata/pgsql_stats_tmp tmpfs size=1G ,uid=enterprisedb,gid=enterprisedb 0 0

  3. Edit postgresql.conf and add this line:

    stats_temp_directory = '/u01/app/pgdata/pgsql_stats_tmp '

  4. Restart the database.

    $ pg_ctl -D /u01/app/pgdata stop -m immediate

    $ pg_ctl -D /u01/app/pgdata start

  5. Verify that the tmpfs is used.

    To verify, run this command:

    -bash-4.1$ psql

    psql.bin (9.5.0.5)

    edb=#

  6. Run this command:

    show stats_temp_directory;

    /u01/app/pgdata/stats_temp_directory

    ---------------------

    pg_stat_tmp

    (1 row)

    edb=#

  7. Run this command:

    \q

  8. Run this command:

    ls -lh /u01/app/pgdata/pg_stat_tmp

    total 12K

    -rw------- 1 enterprisedb enterprisedb 850 Nov 4 09:50 db_0.stat

    -rw------- 1 enterprisedb enterprisedb 2.2K Nov 4 09:50 db_12896.stat

    -rw------- 1 enterprisedb enterprisedb 471 Nov 4 09:50 global.stat

    For more information, see Monitoring-stats.