postgresql
aileen5150
这个作者很懒,什么都没留下…
展开
-
postgres创建外部表
postgres 创建外部表原创 2022-08-27 14:24:40 · 534 阅读 · 0 评论 -
ubuntu 安装pg_basebackup
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -sudo add-apt-repository "deb https://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main"sudo apt-get install postgresql-client--同步主服务器数据sudo pg_basebackup -D.原创 2021-12-14 15:59:51 · 1841 阅读 · 0 评论 -
osm导入到postgresql
osm2pgsql -H 10.10.11.191 -P 5432 -U postgres -W -d maps -O flex -s -S ./osm2pgsql/flex-config/attributes.lua --extra-attributes china-latest.osm.pbf原创 2021-11-29 10:14:24 · 322 阅读 · 0 评论 -
postgresql慢查询
修改postgresql.confshared_preload_libraries ,增加pg_stat_statements加载扩展create extension pg_stat_statements;重置计数器select pg_stat_reset();select pg_stat_statements_reset();查询耗时的sqlselect * from pg_stat_statements order by total_time desc limit 5;查询buffer原创 2021-09-08 15:34:54 · 572 阅读 · 0 评论 -
influx 1.7.7 alpine docker-compose 创建数据库并设置保存策略
version: "3"services: influxdb: image: reg.ji.com/influxdb:1.7.7-alpine restart: always volumes: - "$VOLUME_PATH/influxdb:/var/lib/influxdb" ports: - 8086:8086 #entryp.原创 2021-07-08 15:16:36 · 203 阅读 · 0 评论 -
postgresql 备份恢复
//备份数据库pg_dump "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=postgres password=123456 dbname=postgres " >db.sql//恢复数据库psql -h 127.0.0.1 -U postgres -p 5432 -d postgres < db.sql;原创 2021-07-07 09:53:55 · 82 阅读 · 0 评论 -
crontab-ui docker部署
git clonehttps://github.com/aileen5150/crontab-ui.git,下载crontab-ui相关文件pg_bak.sh内容如下:#!/bin/bash if [ ! -d /db ];then mkdir /db fi cur_time=$(date '+%Y-%m-%d') fifteendays_time=$(date -d-15days '+%Y-%m-%d') rm -rf /db/$fifteendays_time.dump e..原创 2021-06-29 10:49:59 · 421 阅读 · 0 评论 -
postgres 分表 并循环删除
CREATE TABLE apps ( id SERIAL PRIMARY KEY, ts timestamp without time zone);--分表函数CREATE OR REPLACE FUNCTION f_table_split()RETURNS TRIGGER AS $$DECLARE suffix TEXT;DECLARE insert_rec TEXT;DECLARE tablename TEXT;DECLARE notify TEXT;BEGIN...原创 2021-06-07 15:09:42 · 334 阅读 · 0 评论 -
postgresql function 返回查询结果
CREATE OR REPLACE FUNCTION test_xxxx(in_deduces_id bigint,in_no bigint,threshold float)RETURNS table(id bigint,ts TIMESTAMP,deduces_id bigint ,no bigint ,data float[]) AS $$BEGIN RETURN QUERY with cte_x as ( SELECT min(a.data[3]) as min_x,m...原创 2021-05-28 11:20:52 · 549 阅读 · 0 评论 -
postgresql
--查询所有表名SELECT tablename FROM pg_tables WHERE schemaname ='public';--查询所有view名SELECT viewname FROM pg_views WHERE schemaname ='public';--查询表结构SELECT a.attnum,a.attname AS field,t.typname AS...原创 2019-07-25 10:52:03 · 489 阅读 · 0 评论