探索PostgreSQL：实用技巧一览（一）

PostgreSQL的一些使用技巧

一：psql 错误记录

Q1：FATAL: password authentication failed for user “postgres”致命:用户“postgres”的密码验证失败

FATAL: could not map anonymous shared memory: Cannot allocate memory

HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 8924045312 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

修改内存：

grep shared_buffers postgresql.conf

shared_buffers = 128MB # min 128kB

#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers

shared_buffers = 819MB

psql: FATAL: could not access file "$libdir/plugins/rds_extension_list": No such file or directory

注释腾讯云组件

rds_extension_list

FATAL: data directory "/data/game3" has group or world access

DETAIL: Permissions should be u=rwx (0700).

配置目录正确权限

chmod 0700 game3

二：一般命令

##切换用户 postgres 管理用户

su postgres

#进去

psql

创建和删除数据库用户

1.查看数据库用户列表: \du

2.创建数据库用户: create user user1 with password '123456';

3.删除数据库用户: drop user root

创建和删除数据库

1.查看数据库列表: \l (list的意思)

2.创建数据库: create database db1;

3.删除数据库: drop database root

创建和删除 数据库表

1.选择数据库: \c DatabaseName (choose的意思)

2.创建数据库表: create table people;

3.删除数据库表: drop table root

4.查看数据库信息：\d (database list的意思)

###

#查表 \d

psql -U user01 -d dolphinscheduler -h 127.0.0.1

user2023

#建表

CREATE TABLE subjects (id int,name varchar(255),age int,sex int,address varchar(255))

#插入数据

INSERT INTO subjects VALUES(1,'dr3dwdwam',13,3,'Benling');

#查询

select * from subjects;

三：命令行执行sql语句

psql -c "select * from game_monitor;" "host=10.3.1.11 hostaddr=10.3.1.11 port=5432 user=aoagm password=aoa#psql dbname=heros_user"

#!/bin/bash

num=`psql -c "select * from game_monitor where status !=0;" "host=10.3.1.11 hostaddr=10.3.1.11 port=5432 user=aoagm password=aoa#psql dbname=heros_user"|grep -v -E "rows|key_monitor|+|-"`

echo $num

psql -c "select status from game_monitor where status!=0;" "host=10.3.1.11 hostaddr=10.3.1.11 port=5432 user=aoagm password=aoa#psql dbname=heros_user"|grep -v -E "rows|status|-"

psql -c "SELECT client_ip FROM user_info WHERE create_date BETWEEN (select date_trunc('day',now()) -interval '1d') and (select date_trunc('day',now()))" "host=10.3.1.36 hostaddr=10.3.1.36 port=5432 user=aoagm password=aoa#psql dbname=heros_user"

#!/bin/sh

#save to num

num=`psql -c "SELECT client_ip FROM user_info WHERE create_date BETWEEN (select date_trunc('day',now()) -interval '1d') and (select date_trunc('day',now()))" "host=10.3.1.36 hostaddr=10.3.1

.36 port=5432 user=aoagm password=aoa#psql dbname=heros_user"|wc -l`

#save to file

psql -c "SELECT client_ip FROM user_info WHERE create_date BETWEEN (select date_trunc('day',now()) -interval '1d') and (select date_trunc('day',now()))" "host=10.3.1.36 hostaddr=10.3.1.36 p

ort=5432 user=aoagm password=aoa#psql dbname=heros_user" > ip.txt

echo $num

wc -l ip.txt