Every Day of a DBA，第143期: PostgreSQL 19 Beta1 新特性测试

select pg_get_database_ddl(oid)asdatabasefrom pg_database where datname=current_database();

                                                        database                                                        
--------------------------------------------------------------------------------------------------------------------
 CREATE DATABASE bigdata WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
 ALTER DATABASE bigdata OWNER TO postgres;
(2 rows)

CREATETABLESPACE ts_hot LOCATION '/opt/postgresql-19beta1/data/ts_hot';

WARNING:  tablespace location should not be inside the data directory
CREATE TABLESPACE

select*from pg_tablespace ;

  oid  |  spcname   | spcowner | spcacl | spcoptions 
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 16457 | ts_hot     |       10 |        | 
(3 rows)

CREATETABLESPACE ts_cold
LOCATION '/opt/postgresql-19beta1/data/ts_cold'  -- 原测试笔误ts_hot，已修正
WITH(
    io_min_workers =2,
    io_max_workers =8
);

-- 修改最大AIO线程
ALTERTABLESPACE ts_cold SET(io_max_workers =12);
-- 重载配置生效
SELECT pg_reload_conf();

select pg_get_tablespace_ddl(16457);
-- 或按表空间名称过滤
SELECT pg_get_tablespace_ddl(oid)FROM pg_tablespace WHERE spcname ='ts_cold';

                                  pg_get_tablespace_ddl                                  
-----------------------------------------------------------------------------------------
 CREATE TABLESPACE ts_hot OWNER postgres LOCATION '/opt/postgresql-19beta1/data/ts_hot';
(1 row)

ALTERTABLE big_table REPACK TABLESPACE ts_hot;

ALTERTABLE range_t SPLIT PARTITION p1 AT(1000)
INTO(PARTITION p1_1 TABLESPACE ts_hot,PARTITION p1_2 TABLESPACE ts_cold);

select pg_get_role_ddl(16458);

                                         pg_get_role_ddl                                          
--------------------------------------------------------------------------------------------------
 CREATE ROLE admin NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
(1 row)

SELECT
    p.pubid,
    p.relid,
    nsp.nspnameAS schemaname,
    cls.relnameAS tablename
FROM pg_get_publication_tables('pub_all') p
JOIN pg_class cls ON p.relid= cls.oid
JOIN pg_namespace nsp ON cls.relnamespace= nsp.oid;

 pubid | relid | schemaname | tablename 
-------+-------+------------+-----------
 16471 | 16414 | public     | users
 16471 | 16422 | public     | follow
 16471 | 16459 | public     | test_log
 16471 | 16462 | public     | tmp_table
 16471 | 16465 | public     | biz_data
(5 rows)