1. 服务端 ip 10.1.1.66 Docker 容器
PS C:\Users\Mestc> ssh dock
Linux Dock 5.4.34-1-pve #1 SMP PVE 5.4.34-2 (Thu, 07 May 2020 10:02:02 +0200) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Wed May 11 15:22:48 2022 from 10.1.1.211
root@Dock:~# docker run -d --name tsdb01 -p 5450:5432 -e POSTGRES_PASSWORD=szoscar55 timescale/timescaledb:latest-pg14
f8bf1e99691dec53ee215130c2ee13feebe605729440bc0c88668d771e1e08b5
root@Dock:~# docker run -d --name tsdb02 -p 5450:5432 -e POSTGRES_PASSWORD=szoscar55 timescale/timescaledb:latest-pg14
6f6d034e6a1856a057b9fd1641f319cc39622de0fd88acc61de1dfee2a0f12d0
docker: Error response from daemon: driver failed programming external connectivity on endpoint tsdb02 (23384d98425869b2d6d513016d5c078c5305fd1e0bcdd341b747d03e1d861517): Bind for 0.0.0.0:5450 failed: port is already allocated.
root@Dock:~# docker rm tsdb02
tsdb02
root@Dock:~# docker run -d --name tsdb02 -p 5451:5432 -e POSTGRES_PASSWORD=szoscar55 timescale/timescaledb:latest-pg14
1b9e75f1e132c4e3f49e02dbac567bb0f01b6c0e01fa8bc07021844b8c06229c
^[[Aroot@Dock:~# docker run -d --name tsdb03 -p 5453:5432 -e POSTGRES_PASSWORD=szoscar55 timescale/timescaledb:latest-pg14
de679da7239744abb48b7cef2c3f2f033b12b2cba7cbd567e780a3efa6308540
root@Dock:~# docker stop tsdb01
tsdb01
root@Dock:~# docker stop tsdb02
tsdb02
root@Dock:~# docker rm tsdb01
tsdb01
root@Dock:~# docker rm tsdb02
tsdb02
root@Dock:~# docker run -d --name tsdb01 -p 5451:5432 -e POSTGRES_PASSWORD=szoscar55 timescale/timescaledb:latest-pg14
fed6cb6d9c7ea8b93c8be1a3f8fe281662e35227374c79d65468c310e51e2c23
root@Dock:~# docker run -d --name tsdb02 -p 5452:5432 -e POSTGRES_PASSWORD=szoscar55 timescale/timescaledb:latest-pg14
9d73ef9d8ffdcacfc6a9420449d7c6d5d5d2fb957992f41803d3618b21483d7f
root@Dock:~#
root@Dock:~#
root@Dock:~#
root@Dock:~# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9d73ef9d8ffd timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 10 seconds ago Up 5 seconds 0.0.0.0:5452->5432/tcp, :::5452->5432/tcp tsdb02
fed6cb6d9c7e timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 20 seconds ago Up 18 seconds 0.0.0.0:5451->5432/tcp, :::5451->5432/tcp tsdb01
de679da72397 timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 6 minutes ago Up 6 minutes 0.0.0.0:5453->5432/tcp, :::5453->5432/tcp tsdb03
1cd3df1ec445 grafana/grafana-oss "/run.sh" 23 hours ago Up 23 hours 0.0.0.0:3000->3000/tcp, :::3000->3000/tcp grafana
b3cbf0ef06a8 timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 2 weeks ago Up 2 weeks 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp tsdb
3c1925da8e5a mcr.microsoft.com/mssql/server:latest "/opt/mssql/bin/perm…" 3 months ago Up 2 months 0.0.0.0:1433->1433/tcp, :::1433->1433/tcp mssql2019
1ab3a8ad74fb rohitbasu77/oracle11g:latest "/bin/sh -c 'sed -i …" 6 months ago Up 2 months 8080/tcp, 0.0.0.0:2266->22/tcp, :::2266->22/tcp, 0.0.0.0:15210->1521/tcp, :::15210->1521/tcp oracle11g
3b0c5571540c memcached "docker-entrypoint.s…" 6 months ago Up 2 months 0.0.0.0:11211->11211/tcp, :::11211->11211/tcp memcached
fff2cfed8ab9 redis "docker-entrypoint.s…" 6 months ago Up 2 months 0.0.0.0:6379->6379/tcp, :::6379->6379/tcp redis
f8d3730d4b92 banglamon/oracle193db:19.3.0-ee "/bin/sh -c 'exec $O…" 6 months ago Up 2 months (healthy) 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp, 5500/tcp oracle19db
c80b5e4c290c oscardb:202111 "/entrypoint.sh" 6 months ago Up 2 months 0.0.0.0:2003->2003/tcp, :::2003->2003/tcp, 0.0.0.0:52099->52099/tcp, :::52099->52099/tcp oscardb
root@Dock:~# docker run -d --name tsdb04 -p 5454:5432 -e POSTGRES_PASSWORD=szoscar55 timescale/timescaledb:latest-pg14
bb0bbaa12ad172cc1aba55bec4f0baf6b699c0b968e1d692c507caf2dcd15299
root@Dock:~#
root@Dock:~#
root@Dock:~# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bb0bbaa12ad1 timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 6 seconds ago Up 5 seconds 0.0.0.0:5454->5432/tcp, :::5454->5432/tcp tsdb04
9d73ef9d8ffd timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 43 seconds ago Up 38 seconds 0.0.0.0:5452->5432/tcp, :::5452->5432/tcp tsdb02
fed6cb6d9c7e timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 53 seconds ago Up 51 seconds 0.0.0.0:5451->5432/tcp, :::5451->5432/tcp tsdb01
de679da72397 timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 7 minutes ago Up 7 minutes 0.0.0.0:5453->5432/tcp, :::5453->5432/tcp tsdb03
1cd3df1ec445 grafana/grafana-oss "/run.sh" 23 hours ago Up 23 hours 0.0.0.0:3000->3000/tcp, :::3000->3000/tcp grafana
b3cbf0ef06a8 timescale/timescaledb:latest-pg14 "docker-entrypoint.s…" 2 weeks ago Up 2 weeks 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp tsdb
3c1925da8e5a mcr.microsoft.com/mssql/server:latest "/opt/mssql/bin/perm…" 3 months ago Up 2 months 0.0.0.0:1433->1433/tcp, :::1433->1433/tcp mssql2019
1ab3a8ad74fb rohitbasu77/oracle11g:latest "/bin/sh -c 'sed -i …" 6 months ago Up 2 months 8080/tcp, 0.0.0.0:2266->22/tcp, :::2266->22/tcp, 0.0.0.0:15210->1521/tcp, :::15210->1521/tcp oracle11g
3b0c5571540c memcached "docker-entrypoint.s…" 6 months ago Up 2 months 0.0.0.0:11211->11211/tcp, :::11211->11211/tcp memcached
fff2cfed8ab9 redis "docker-entrypoint.s…" 6 months ago Up 2 months 0.0.0.0:6379->6379/tcp, :::6379->6379/tcp redis
f8d3730d4b92 banglamon/oracle193db:19.3.0-ee "/bin/sh -c 'exec $O…" 6 months ago Up 2 months (healthy) 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp, 5500/tcp oracle19db
c80b5e4c290c oscardb:202111 "/entrypoint.sh" 6 months ago Up 2 months 0.0.0.0:2003->2003/tcp, :::2003->2003/tcp, 0.0.0.0:52099->52099/tcp, :::52099->52099/tcp oscardb
root@Dock:~#
root@Dock:~#
root@Dock:~#
root@Dock:~# ls
data.cap oscardb-docker oscardb-docker.tar.gz
root@Dock:~#
root@Dock:~#
root@Dock:~#
root@Dock:~# docker exec -it tsdb01 bash
bash-5.1# nano /var/lib/postgresql/data/postgresql.conf
bash: nano: command not found
bash-5.1# vi /var/lib/postgresql/data/postgresql.conf
bash-5.1# vi /var/lib/postgresql/data/postgresql.conf
bash-5.1#
bash-5.1#
bash-5.1# vi /var/lib/postgresql/data/postgresql.conf
bash-5.1#
bash-5.1#
bash-5.1# exit
exit
root@Dock:~# docker restart tsdb01
tsdb01
root@Dock:~# docker exec -it tsdb02 bash
bash-5.1# vi /var/lib/postgresql/data/postgresql.conf
bash-5.1# exit
exit
root@Dock:~# docker restart tsdb02
tsdb02
root@Dock:~# docker exec -it tsdb03 bash
bash-5.1# vi /var/lib/postgresql/data/postgresql.conf
bash-5.1# vim --version
bash: vim: command not found
bash-5.1# exit
exit
root@Dock:~# docker restart tsdb03
tsdb03
root@Dock:~# docker exec -it tsdb04 bash
bash-5.1# vi /var/lib/postgresql/data/postgresql.conf
bash-5.1# vi /var/lib/postgresql/data/passfile
bash-5.1# chmod 0600 /var/lib/postgresql/data/passfile
bash-5.1# exit
exit
root@Dock:~# docker restart tsdb04
tsdb04
root@Dock:~# docker stop tsdb01
tsdb01
root@Dock:~# docker restart tsdb01
tsdb01
root@Dock:~# docker stop tsdb03
tsdb03
root@Dock:~# docker restart tsdb03
tsdb03
root@Dock:~#
tsdb01,02,03 数据节点 postgresql.conf 添加内容
vi /var/lib/postgresql/data/postgresql.conf
# Custom settings
max_prepared_transactions = 150
wal_level='logical'
max_wal_senders = 10
max_replication_slots = 10
tsdb04 访问节点 postgresql.conf 添加内容
vi /var/lib/postgresql/data/postgresql.conf
timescaledb.passfile = 'passfile'
vi /var/lib/postgresql/data/passfile
*:*:*:postgres:szoscar55
2. psql 客户端
root@deb10:~# which psql
/usr/local/pgsql/bin/psql
root@deb10:~# ls /usr/local/pgsql/
bin data include lib share
root@deb10:~# ls /usr/local/pgsql/bin
clusterdb createuser dropuser initdb pg_archivecleanup pgbench pg_config pg_ctl pg_dumpall pg_receivewal pg_resetwal pg_rewind pg_test_timing pg_verifybackup postgres psql vacuumdb
createdb dropdb ecpg pg_amcheck pg_basebackup pg_checksums pg_controldata pg_dump pg_isready pg_recvlogical pg_restore pg_test_fsync pg_upgrade pg_waldump postmaster reindexdb
root@deb10:~# pg
pg_amcheck pgbench pg_config.libpq-dev pg_createcluster pg_dropcluster pg_isready pg_recvlogical pg_resetwal pg_test_fsync pg_upgrade pg_virtualenv
pg_archivecleanup pg_checksums pg_conftool pg_ctl pg_dump pg_lsclusters pg_renamecluster pg_restore pg_test_timing pg_upgradecluster pg_waldump
pg_basebackup pg_config pg_controldata pg_ctlcluster pg_dumpall pg_receivewal pgrep pg_rewind pg_updatedicts pg_verifybackup
root@deb10:~# psql -h
psql: option requires an argument -- 'h'
Try "psql --help" for more information.
root@deb10:~# psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "root")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (Comma-Separated Values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "root")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5454
Password for user postgres:
psql (14.1, server 14.2)
Type "help" for help.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+-------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.6.1 | public | Enables scalable inserts and complex queries for time-series data
(2 rows)
postgres=# SHOW hba_file;
hba_file
--------------------------------------
/var/lib/postgresql/data/pg_hba.conf
(1 row)
postgres=# SELECT hypertable_name, data_nodes
postgres-# FROM timescaledb_information.hypertables;
hypertable_name | data_nodes
-----------------+------------
(0 rows)
postgres=# SELECT *
FROM timescaledb_information.hypertables;
hypertable_schema | hypertable_name | owner | num_dimensions | num_chunks | compression_enabled | is_distributed | replication_factor | data_nodes | tablespaces
-------------------+-----------------+-------+----------------+------------+---------------------+----------------+--------------------+------------+-------------
(0 rows)
postgres=# SELECT add_data_node('DN01', host => '10.1.1.66', database => 'postgres', port => 5451, password => 'szoscar55');
NOTICE: database "postgres" already exists on data node, skipping
NOTICE: extension "timescaledb" already exists on data node, skipping
DETAIL: TimescaleDB extension version on 10.1.1.66:5451 was 2.6.1.
ERROR: cannot add "DN01" as a data node
DETAIL: ERROR: prepared transactions need to be enabled
DETAIL: Parameter max_prepared_transactions=0.
HINT: Configuration parameter max_prepared_transactions must be set >0 (changes will require restart).
postgres=# show postgresql.conf
postgres-#
postgres-#
postgres-#
postgres-#
postgres-# ;
ERROR: unrecognized configuration parameter "postgresql.conf"
postgres=# show postgresql;
ERROR: unrecognized configuration parameter "postgresql"
postgres=# show postgres.conf;
ERROR: unrecognized configuration parameter "postgres.conf"
postgres=# SHOW config_file;
config_file
------------------------------------------
/var/lib/postgresql/data/postgresql.conf
(1 row)
postgres=# show config_file;
config_file
------------------------------------------
/var/lib/postgresql/data/postgresql.conf
(1 row)
postgres=# show postgresql.conf;
ERROR: unrecognized configuration parameter "postgresql.conf"
postgres=# show /var/lib/postgresql/data/postgresql.conf;
ERROR: syntax error at or near "/"
LINE 1: show /var/lib/postgresql/data/postgresql.conf;
^
postgres=# show postgresql.conf max_prepared_transactions;
ERROR: syntax error at or near "max_prepared_transactions"
LINE 1: show postgresql.conf max_prepared_transactions;
^
postgres=# select name, context from pg_settings
postgres-# show postgresql.conf max_prepared_transactions;
ERROR: syntax error at or near "postgresql"
LINE 2: show postgresql.conf max_prepared_transactions;
^
postgres=# select name, context from pg_settings;
name | context
----------------------------------------------+-------------------
allow_system_table_mods | superuser
application_name | user
archive_cleanup_command | sighup
archive_command | sighup
archive_mode | postmaster
archive_timeout | sighup
array_nulls | user
authentication_timeout | sighup
autovacuum | sighup
autovacuum_analyze_scale_factor | sighup
autovacuum_analyze_threshold | sighup
autovacuum_freeze_max_age | postmaster
autovacuum_max_workers | postmaster
autovacuum_multixact_freeze_max_age | postmaster
autovacuum_naptime | sighup
autovacuum_vacuum_cost_delay | sighup
autovacuum_vacuum_cost_limit | sighup
autovacuum_vacuum_insert_scale_factor | sighup
autovacuum_vacuum_insert_threshold | sighup
autovacuum_vacuum_scale_factor | sighup
autovacuum_vacuum_threshold | sighup
autovacuum_work_mem | sighup
backend_flush_after | user
backslash_quote | user
backtrace_functions | superuser
bgwriter_delay | sighup
bgwriter_flush_after | sighup
bgwriter_lru_maxpages | sighup
bgwriter_lru_multiplier | sighup
block_size | internal
bonjour | postmaster
bonjour_name | postmaster
bytea_output | user
check_function_bodies | user
checkpoint_completion_target | sighup
checkpoint_flush_after | sighup
checkpoint_timeout | sighup
checkpoint_warning | sighup
client_connection_check_interval | user
client_encoding | user
client_min_messages | user
cluster_name | postmaster
commit_delay | superuser
commit_siblings | user
compute_query_id | superuser
config_file | postmaster
constraint_exclusion | user
cpu_index_tuple_cost | user
cpu_operator_cost | user
cpu_tuple_cost | user
cursor_tuple_fraction | user
data_checksums | internal
data_directory | postmaster
data_directory_mode | internal
data_sync_retry | postmaster
DateStyle | user
db_user_namespace | sighup
deadlock_timeout | superuser
debug_assertions | internal
debug_discard_caches | superuser
debug_pretty_print | user
debug_print_parse | user
debug_print_plan | user
debug_print_rewritten | user
default_statistics_target | user
default_table_access_method | user
default_tablespace | user
default_text_search_config | user
default_toast_compression | user
default_transaction_deferrable | user
default_transaction_isolation | user
default_transaction_read_only | user
dynamic_library_path | superuser
dynamic_shared_memory_type | postmaster
effective_cache_size | user
effective_io_concurrency | user
enable_async_append | user
enable_bitmapscan | user
enable_gathermerge | user
enable_hashagg | user
enable_hashjoin | user
enable_incremental_sort | user
enable_indexonlyscan | user
enable_indexscan | user
enable_material | user
enable_memoize | user
enable_mergejoin | user
enable_nestloop | user
enable_parallel_append | user
enable_parallel_hash | user
enable_partition_pruning | user
enable_partitionwise_aggregate | user
enable_partitionwise_join | user
enable_seqscan | user
postgres=# select name, context from pg_settings where name = 'max_prepared_transactions';
name | context
---------------------------+------------
max_prepared_transactions | postmaster
(1 row)
postgres=# select * from pg_settings where name = 'max_prepared_transactions';
name | setting | unit | category | short_desc
| extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
---------------------------+---------+------+-------------------------+------------------------------------------------------------------+------------+------------+---------+---------+---------+---------+----------+----------+-----------+------------+------------+-----------------
max_prepared_transactions | 0 | | Resource Usage / Memory | Sets the maximum number of simultaneously prepared transactions. | | postmaster | integer | default | 0 | 262143 | | 0 | 0 | | | f
(1 row)
postgres=#
postgres=#
postgres=#
postgres=# select * from pg_settings where name = 'max_prepared_transactions';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
---------------------------+---------+------+-------------------------+------------------------------------------------------------------+------------+------------+---------+---------+---------+---------+----------+----------+-----------+------------+------------+-----------------
max_prepared_transactions | 0 | | Resource Usage / Memory | Sets the maximum number of simultaneously prepared transactions. | | postmaster | integer | default | 0 | 262143 | | 0 | 0 | | | f
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'max_prepared_transactions';
name | setting | vartype | min_val | max_val
---------------------------+---------+---------+---------+---------
max_prepared_transactions | 0 | integer | 0 | 262143
(1 row)
postgres=# set max_prepared_transactions=150;
ERROR: parameter "max_prepared_transactions" cannot be changed without restarting the server
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'max_prepared_transactions';
name | setting | vartype | min_val | max_val
---------------------------+---------+---------+---------+---------
max_prepared_transactions | 0 | integer | 0 | 262143
(1 row)
postgres=# set enable_partitionwise_aggregate='on';
SET
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'enable_partitionwise_aggregate';
name | setting | vartype | min_val | max_val
--------------------------------+---------+---------+---------+---------
enable_partitionwise_aggregate | on | bool | |
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'jit';
name | setting | vartype | min_val | max_val
------+---------+---------+---------+---------
jit | on | bool | |
(1 row)
postgres=# set jit='off';
SET
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'statement_timeout';
name | setting | vartype | min_val | max_val
-------------------+---------+---------+---------+------------
statement_timeout | 0 | integer | 0 | 2147483647
(1 row)
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'statement_timeout';
name | setting | vartype | min_val | max_val
-------------------+---------+---------+---------+------------
statement_timeout | 0 | integer | 0 | 2147483647
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'jit';
name | setting | vartype | min_val | max_val
------+---------+---------+---------+---------
jit | off | bool | |
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'enable_partitionwise_aggregate';
name | setting | vartype | min_val | max_val
--------------------------------+---------+---------+---------+---------
enable_partitionwise_aggregate | on | bool | |
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'max_prepared_transactions';
name | setting | vartype | min_val | max_val
---------------------------+---------+---------+---------+---------
max_prepared_transactions | 0 | integer | 0 | 262143
(1 row)
postgres=# \q
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5451
Password for user postgres:
psql (14.1, server 14.2)
Type "help" for help.
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'max_prepared_transactions';
name | setting | vartype | min_val | max_val
---------------------------+---------+---------+---------+---------
max_prepared_transactions | 0 | integer | 0 | 262143
(1 row)
postgres=# set max_prepared_transactions=150;
ERROR: parameter "max_prepared_transactions" cannot be changed without restarting the server
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'jit';
name | setting | vartype | min_val | max_val
------+---------+---------+---------+---------
jit | on | bool | |
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'wal_level';
name | setting | vartype | min_val | max_val
-----------+---------+---------+---------+---------
wal_level | replica | enum | |
(1 row)
postgres=# show config_file
postgres-# ;
config_file
------------------------------------------
/var/lib/postgresql/data/postgresql.conf
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'wal_level';
name | setting | vartype | min_val | max_val
-----------+---------+---------+---------+---------
wal_level | replica | enum | |
(1 row)
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'wal_level';
name | setting | vartype | min_val | max_val | enumvals
-----------+---------+---------+---------+---------+---------------------------
wal_level | replica | enum | | | {minimal,replica,logical}
(1 row)
postgres=# set wal_level='logical';
ERROR: parameter "wal_level" cannot be changed without restarting the server
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'max_wal_senders';
name | setting | vartype | min_val | max_val | enumvals
-----------------+---------+---------+---------+---------+----------
max_wal_senders | 10 | integer | 0 | 262143 |
(1 row)
postgres=# set max_wal_senders=20;
ERROR: parameter "max_wal_senders" cannot be changed without restarting the server
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'max_replication_slots';
name | setting | vartype | min_val | max_val | enumvals
-----------------------+---------+---------+---------+---------+----------
max_replication_slots | 10 | integer | 0 | 262143 |
(1 row)
postgres=# set max_replication_slots=20;
ERROR: parameter "max_replication_slots" cannot be changed without restarting the server
postgres=#
postgres=#
postgres=#
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'max_replication_slots';
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
psql (14.1, server 14.2)
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'max_replication_slots';
name | setting | vartype | min_val | max_val | enumvals
-----------------------+---------+---------+---------+---------+----------
max_replication_slots | 10 | integer | 0 | 262143 |
(1 row)
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'wal_level';
name | setting | vartype | min_val | max_val | enumvals
-----------+---------+---------+---------+---------+---------------------------
wal_level | logical | enum | | | {minimal,replica,logical}
(1 row)
postgres=# select name, setting, vartype, min_val, max_val from pg_settings where name = 'max_prepared_transactions';
name | setting | vartype | min_val | max_val
---------------------------+---------+---------+---------+---------
max_prepared_transactions | 150 | integer | 0 | 262143
(1 row)
postgres=# \q
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5454
Password for user postgres:
psql (14.1, server 14.2)
Type "help" for help.
postgres=# SELECT add_data_node('DN01', host => '10.1.1.66', database => 'postgres', port => 5451, password => 'szoscar55');
NOTICE: database "postgres" already exists on data node, skipping
NOTICE: extension "timescaledb" already exists on data node, skipping
DETAIL: TimescaleDB extension version on 10.1.1.66:5451 was 2.6.1.
add_data_node
--------------------------------------
(DN01,10.1.1.66,5451,postgres,t,f,f)
(1 row)
postgres=# SELECT add_data_node('DN02', host => '10.1.1.66', database => 'postgres', port => 5452, password => 'szoscar55');
NOTICE: database "postgres" already exists on data node, skipping
NOTICE: extension "timescaledb" already exists on data node, skipping
DETAIL: TimescaleDB extension version on 10.1.1.66:5452 was 2.6.1.
add_data_node
--------------------------------------
(DN02,10.1.1.66,5452,postgres,t,f,f)
(1 row)
postgres=# SELECT add_data_node('DN03', host => '10.1.1.66', database => 'postgres', port => 5453, password => 'szoscar55');
NOTICE: database "postgres" already exists on data node, skipping
NOTICE: extension "timescaledb" already exists on data node, skipping
DETAIL: TimescaleDB extension version on 10.1.1.66:5453 was 2.6.1.
add_data_node
--------------------------------------
(DN03,10.1.1.66,5453,postgres,t,f,f)
(1 row)
postgres=#
postgres=#
postgres=# SELECT * FROM timescaledb_information.data_nodes;
node_name | owner | options
-----------+----------+--------------------------------------------
DN01 | postgres | {host=10.1.1.66,port=5451,dbname=postgres}
DN02 | postgres | {host=10.1.1.66,port=5452,dbname=postgres}
DN03 | postgres | {host=10.1.1.66,port=5453,dbname=postgres}
(3 rows)
postgres=# select * from timescaledb_information.hypertables;
hypertable_schema | hypertable_name | owner | num_dimensions | num_chunks | compression_enabled | is_distributed | replication_factor | data_nodes | tablespaces
-------------------+-----------------+-------+----------------+------------+---------------------+----------------+--------------------+------------+-------------
(0 rows)
postgres=# SELECT * FROM timescaledb_information.chunks;
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | data_nodes
-------------------+-----------------+--------------+------------+-------------------+------------------------+-------------+-----------+---------------------+-------------------+---------------+------------------+------------
(0 rows)
postgres=#
postgres=#
postgres=#
postgres=# SELECT * FROM timescaledb_information.data_nodes;
node_name | owner | options
-----------+----------+--------------------------------------------
DN01 | postgres | {host=10.1.1.66,port=5451,dbname=postgres}
DN02 | postgres | {host=10.1.1.66,port=5452,dbname=postgres}
DN03 | postgres | {host=10.1.1.66,port=5453,dbname=postgres}
(3 rows)
postgres=# create table ts_data (time timestamp, name varchar(64), val varchar(128), num decimal);
CREATE TABLE
postgres=# select create_distributed_hypertable('ts_data', 'time', 'name', chunk_time_interval => INTERVAL '10 seconds', replication_factor => 3);
NOTICE: adding not-null constraint to column "time"
DETAIL: Time dimensions cannot have NULL values.
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: fe_sendauth: no password supplied
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'timescaledb.passfile';
name | setting | vartype | min_val | max_val | enumvals
----------------------+---------+---------+---------+---------+----------
timescaledb.passfile | | string | | |
(1 row)
postgres=# set timescaledb.passfile='passfile';
ERROR: parameter "timescaledb.passfile" cannot be changed now
postgres=#
postgres=#
postgres=#
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'timescaledb.passfile';
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
psql (14.1, server 14.2)
postgres=# select name, setting, vartype, min_val, max_val, enumvals from pg_settings where name = 'timescaledb.passfile';
name | setting | vartype | min_val | max_val | enumvals
----------------------+----------+---------+---------+---------+----------
timescaledb.passfile | passfile | string | | |
(1 row)
postgres=# select create_distributed_hypertable('ts_data', 'time', 'name', chunk_time_interval => INTERVAL '10 seconds', replication_factor => 3);
NOTICE: adding not-null constraint to column "time"
DETAIL: Time dimensions cannot have NULL values.
create_distributed_hypertable
-------------------------------
(2,public,ts_data,t)
(1 row)
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# drop table ts_data;
DROP TABLE
postgres=# SELECT * FROM timescaledb_information.data_nodes;
node_name | owner | options
-----------+----------+--------------------------------------------
DN01 | postgres | {host=10.1.1.66,port=5451,dbname=postgres}
DN02 | postgres | {host=10.1.1.66,port=5452,dbname=postgres}
DN03 | postgres | {host=10.1.1.66,port=5453,dbname=postgres}
(3 rows)
postgres=# select * from timescaledb_information.hypertables;
hypertable_schema | hypertable_name | owner | num_dimensions | num_chunks | compression_enabled | is_distributed | replication_factor | data_nodes | tablespaces
-------------------+-----------------+-------+----------------+------------+---------------------+----------------+--------------------+------------+-------------
(0 rows)
postgres=# create table ts_data (time timestamp not null, name varchar(64) not null, val varchar(128), num decimal);
CREATE TABLE
postgres=# select create_distributed_hypertable('ts_data', 'time', 'name', chunk_time_interval => INTERVAL '10 seconds', replication_factor => 3);
create_distributed_hypertable
-------------------------------
(3,public,ts_data,t)
(1 row)
postgres=#
postgres=#
postgres=#
postgres=# select * from timescaledb_information.hypertables;
hypertable_schema | hypertable_name | owner | num_dimensions | num_chunks | compression_enabled | is_distributed | replication_factor | data_nodes | tablespaces
-------------------+-----------------+----------+----------------+------------+---------------------+----------------+--------------------+------------------+-------------
public | ts_data | postgres | 2 | 0 | f | t | 3 | {DN01,DN02,DN03} |
(1 row)
postgres=# SELECT * FROM timescaledb_information.chunks;
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | data_nodes
-------------------+-----------------+--------------+------------+-------------------+------------------------+-------------+-----------+---------------------+-------------------+---------------+------------------+------------
(0 rows)
postgres=# insert into ts_data values ('2022-05-12 15:00:00', 'zengling', 'hello world.', 28188.22),('2022-05-12 15:00:01', 'zhangsan', 'hello.', 28228.35), ('2022-05-12 15:00:12', 'zhouli', 'hello world.', 281.20);
INSERT 0 3
postgres=# SELECT * FROM timescaledb_information.chunks;
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | data_nodes
-------------------+-----------------+-----------------------+-----------------------+-------------------+-----------------------------+------------------------+------------------------+---------------------+-------------------+---------------+------------------+------------------
public | ts_data | _timescaledb_internal | _dist_hyper_3_1_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | | {DN01,DN02,DN03}
public | ts_data | _timescaledb_internal | _dist_hyper_3_2_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | | {DN01,DN02,DN03}
public | ts_data | _timescaledb_internal | _dist_hyper_3_3_chunk | time | timestamp without time zone | 2022-05-12 15:00:10+00 | 2022-05-12 15:00:20+00 | | | f | | {DN01,DN02,DN03}
(3 rows)
postgres=# SELECT * FROM timescaledb_information.chunks;
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | data_nodes
-------------------+-----------------+-----------------------+-----------------------+-------------------+-----------------------------+------------------------+------------------------+---------------------+-------------------+---------------+------------------+------------------
public | ts_data | _timescaledb_internal | _dist_hyper_3_1_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | | {DN01,DN02,DN03}
public | ts_data | _timescaledb_internal | _dist_hyper_3_2_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | | {DN01,DN02,DN03}
public | ts_data | _timescaledb_internal | _dist_hyper_3_3_chunk | time | timestamp without time zone | 2022-05-12 15:00:10+00 | 2022-05-12 15:00:20+00 | | | f | | {DN01,DN02,DN03}
(3 rows)
postgres=# select show_chunks('ts_data');
show_chunks
---------------------------------------------
_timescaledb_internal._dist_hyper_3_1_chunk
_timescaledb_internal._dist_hyper_3_2_chunk
_timescaledb_internal._dist_hyper_3_3_chunk
(3 rows)
postgres=# SELECT * FROM timescaledb_information.nodes;
ERROR: relation "timescaledb_information.nodes" does not exist
LINE 1: SELECT * FROM timescaledb_information.nodes;
^
postgres=# SELECT * FROM timescaledb_information.data_nodes;
node_name | owner | options
-----------+----------+--------------------------------------------
DN01 | postgres | {host=10.1.1.66,port=5451,dbname=postgres}
DN02 | postgres | {host=10.1.1.66,port=5452,dbname=postgres}
DN03 | postgres | {host=10.1.1.66,port=5453,dbname=postgres}
(3 rows)
postgres=# SELECT * from timescaledb_information.dimensions;
hypertable_schema | hypertable_name | dimension_number | column_name | column_type | dimension_type | time_interval | integer_interval | integer_now_func | num_partitions
-------------------+-----------------+------------------+-------------+-----------------------------+----------------+---------------+------------------+------------------+----------------
public | ts_data | 1 | time | timestamp without time zone | Time | 00:00:10 | | |
public | ts_data | 2 | name | character varying | Space | | | | 3
(2 rows)
postgres=#
postgres=#
postgres=# SELECT * FROM timescaledb_information.chunks;
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | data_nodes
-------------------+-----------------+-----------------------+-----------------------+-------------------+-----------------------------+------------------------+------------------------+---------------------+-------------------+---------------+------------------+------------------
public | ts_data | _timescaledb_internal | _dist_hyper_3_1_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | | {DN01,DN02,DN03}
public | ts_data | _timescaledb_internal | _dist_hyper_3_2_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | | {DN01,DN02,DN03}
public | ts_data | _timescaledb_internal | _dist_hyper_3_3_chunk | time | timestamp without time zone | 2022-05-12 15:00:10+00 | 2022-05-12 15:00:20+00 | | | f | | {DN01,DN02,DN03}
(3 rows)
postgres=# select * from timescaledb_information.hypertables;
hypertable_schema | hypertable_name | owner | num_dimensions | num_chunks | compression_enabled | is_distributed | replication_factor | data_nodes | tablespaces
-------------------+-----------------+----------+----------------+------------+---------------------+----------------+--------------------+------------------+-------------
public | ts_data | postgres | 2 | 3 | f | t | 3 | {DN01,DN02,DN03} |
(1 row)
postgres=# \q
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5451
Password for user postgres:
psql: error: connection to server at "10.1.1.66", port 5451 failed: FATAL: password authentication failed for user "postgres"
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5451
Password for user postgres:
psql (14.1, server 14.2)
Type "help" for help.
postgres=# select * from timescaledb_information.hypertables;
hypertable_schema | hypertable_name | owner | num_dimensions | num_chunks | compression_enabled | is_distributed | replication_factor | data_nodes | tablespaces
-------------------+-----------------+----------+----------------+------------+---------------------+----------------+--------------------+------------+-------------
public | ts_data | postgres | 2 | 3 | f | f | -1 | |
(1 row)
postgres=# SELECT * FROM timescaledb_information.chunks;
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | data_nodes
-------------------+-----------------+-----------------------+-----------------------+-------------------+-----------------------------+------------------------+------------------------+---------------------+-------------------+---------------+------------------+------------
public | ts_data | _timescaledb_internal | _dist_hyper_3_1_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | |
public | ts_data | _timescaledb_internal | _dist_hyper_3_2_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | |
public | ts_data | _timescaledb_internal | _dist_hyper_3_3_chunk | time | timestamp without time zone | 2022-05-12 15:00:10+00 | 2022-05-12 15:00:20+00 | | | f | |
(3 rows)
postgres=# SELECT * from timescaledb_information.dimensions;
hypertable_schema | hypertable_name | dimension_number | column_name | column_type | dimension_type | time_interval | integer_interval | integer_now_func | num_partitions
-------------------+-----------------+------------------+-------------+-----------------------------+----------------+---------------+------------------+------------------+----------------
public | ts_data | 1 | time | timestamp without time zone | Time | 00:00:10 | | |
public | ts_data | 2 | name | character varying | Space | | | | 3
(2 rows)
postgres=# SELECT * FROM timescaledb_information.data_nodes;
node_name | owner | options
-----------+-------+---------
(0 rows)
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# \q
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5454
Password for user postgres:
psql (14.1, server 14.2)
Type "help" for help.
postgres=#
postgres=#
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# select * from ts_data;
ERROR: [DN01]: FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
postgres=# select * from ts_data;
ERROR: connection to data node "DN01" was lost
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN01"
DETAIL: connection to server at "10.1.1.66", port 5451 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# select * from ts_data;
ERROR: [DN03]: FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
postgres=# select * from ts_data;
ERROR: connection to data node "DN03" was lost
postgres=# select * from ts_data;
ERROR: could not connect to "DN03"
DETAIL: connection to server at "10.1.1.66", port 5453 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
ERROR: could not connect to "DN03"
DETAIL: connection to server at "10.1.1.66", port 5453 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# \q
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5452
Password for user postgres:
psql (14.1, server 14.2)
Type "help" for help.
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# \q
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5453
Password for user postgres:
psql (14.1, server 14.2)
Type "help" for help.
postgres=# select * from ts_data;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(3 rows)
postgres=# \q
root@deb10:~# psql -U postgres -h 10.1.1.66 -p 5454
Password for user postgres:
psql (14.1, server 14.2)
Type "help" for help.
postgres=# drop table ts_data;
DROP TABLE
postgres=# SELECT * FROM timescaledb_information.data_nodes;
node_name | owner | options
-----------+----------+--------------------------------------------
DN01 | postgres | {host=10.1.1.66,port=5451,dbname=postgres}
DN02 | postgres | {host=10.1.1.66,port=5452,dbname=postgres}
DN03 | postgres | {host=10.1.1.66,port=5453,dbname=postgres}
(3 rows)
postgres=# SELECT * FROM timescaledb_information.chunks;
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | data_nodes
-------------------+-----------------+--------------+------------+-------------------+------------------------+-------------+-----------+---------------------+-------------------+---------------+------------------+------------
(0 rows)
postgres=# create table ts_data (time timestamp not null, name varchar(64) not null, val varchar(128), num decimal);
CREATE TABLE
postgres=# select create_distributed_hypertable('ts_data', 'time', 'name', chunk_time_interval => INTERVAL '10 seconds', replication_factor => 2);
create_distributed_hypertable
-------------------------------
(4,public,ts_data,t)
(1 row)
postgres=# insert into ts_data values ('2022-05-12 15:00:00', 'zengling', 'hello world.', 28188.22),('2022-05-12 15:00:01', 'zhangsan', 'hello.', 28228.35), ('2022-05-12 15:00:12', 'zhouli', 'hello world.', 281.20);
INSERT 0 3
postgres=# SELECT * FROM timescaledb_information.data_nodes;
node_name | owner | options
-----------+----------+--------------------------------------------
DN01 | postgres | {host=10.1.1.66,port=5451,dbname=postgres}
DN02 | postgres | {host=10.1.1.66,port=5452,dbname=postgres}
DN03 | postgres | {host=10.1.1.66,port=5453,dbname=postgres}
(3 rows)
postgres=# SELECT * FROM timescaledb_information.chunks;
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compressed | chunk_tablespace | data_nodes
-------------------+-----------------+-----------------------+-----------------------+-------------------+-----------------------------+------------------------+------------------------+---------------------+-------------------+---------------+------------------+-------------
public | ts_data | _timescaledb_internal | _dist_hyper_4_4_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | | {DN02,DN03}
public | ts_data | _timescaledb_internal | _dist_hyper_4_5_chunk | time | timestamp without time zone | 2022-05-12 15:00:00+00 | 2022-05-12 15:00:10+00 | | | f | | {DN01,DN02}
public | ts_data | _timescaledb_internal | _dist_hyper_4_6_chunk | time | timestamp without time zone | 2022-05-12 15:00:10+00 | 2022-05-12 15:00:20+00 | | | f | | {DN01,DN03}
(3 rows)
postgres=# select * from _timescaledb_internal._dist_hyper_4_4_chunk;
time | name | val | num
---------------------+----------+--------------+----------
2022-05-12 15:00:00 | zengling | hello world. | 28188.22
(1 row)
postgres=# select * from _timescaledb_internal._dist_hyper_4_5_chunk;
time | name | val | num
---------------------+----------+--------+----------
2022-05-12 15:00:01 | zhangsan | hello. | 28228.35
(1 row)
postgres=# select * from _timescaledb_internal._dist_hyper_4_6_chunk;
time | name | val | num
---------------------+--------+--------------+--------
2022-05-12 15:00:12 | zhouli | hello world. | 281.20
(1 row)
postgres=#
