Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit89b5bd1

Browse files
msdoustiNikolayS
authored andcommitted
Days 86-92
1 parentfbd7009 commit89b5bd1

9 files changed

+738
-0
lines changed

‎0086_how_to_make_e_work_in_psql.md

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
Originally from:[tweet](https://twitter.com/samokhvalov/status/1738267148395688349),[LinkedIn post]().
2+
3+
---
4+
5+
#How to make "\e" work in psql on a new machine ("editor/nano/vi not found")
6+
7+
>I post a new PostgreSQL "howto" article every day. Join me in this
8+
>journey –[subscribe](https://twitter.com/samokhvalov/), provide feedback, share!
9+
10+
Sometimes this happens, when you're attempting to edit a query in psql using the`\e` command:
11+
12+
```
13+
nik=# \e
14+
/usr/bin/sensible-editor: 20: editor: not found
15+
/usr/bin/sensible-editor: 31: nano: not found
16+
/usr/bin/sensible-editor: 20: nano-tiny: not found
17+
/usr/bin/sensible-editor: 20: vi: not found
18+
Couldn't find an editor!
19+
Set the $EDITOR environment variable to your desired editor.
20+
```
21+
22+
Setting the editor is simple (use`nano` or another editor you prefer):
23+
24+
```
25+
\setenv PSQL_EDITOR vim
26+
```
27+
28+
But if you work inside a container, or on a new machine, the desired editor might not yet be installed. You can install
29+
it without leaving`psql`, assuming that there are enough permissions to run installation. For example, inside a
30+
"standard" Postgres, Debian-based (`sudo` is not needed here):
31+
32+
```
33+
nik=# \! apt update && apt install -y vim
34+
```
35+
36+
👉 And then`\e` starts working!
37+
38+
To make this setting persistent, add this to`~/.bash_profile` (or`~/.zprofile`):
39+
40+
```bash
41+
echo"export PSQL_EDITOR=vim">>~/.bash_profile
42+
source~/.bash_profile
43+
```
44+
45+
For Windows, see
46+
[this blog post](https://cybertec-postgresql.com/en/psql_editor-fighting-with-sublime-text-under-windows/)
47+
by[@PavloGolub](https://twitter.com/PavloGolub).
48+
49+
Docs:https://postgresql.org/docs/current/app-psql.html
Lines changed: 152 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,152 @@
1+
Originally from:[tweet](https://twitter.com/samokhvalov/status/1738464958386737401),[LinkedIn post]().
2+
3+
---
4+
5+
#How to change ownership of all objects in a database
6+
7+
>I post a new PostgreSQL "howto" article every day. Join me in this
8+
>journey –[subscribe](https://twitter.com/samokhvalov/), provide feedback, share!
9+
10+
If you need to change ownership of*all* database objects in current database, use this anonymous`DO`
11+
block (or copy-paste from[here](https://gitlab.com/postgres-ai/database-lab/-/snippets/2075222)):
12+
13+
```sql
14+
do $$
15+
declare
16+
new_ownertext :='NEW_OWNER_ROLE_NAME';
17+
object_type record;
18+
r record;
19+
sqltext;
20+
begin
21+
-- New owner for all schemas
22+
for rinselect*from pg_namespace loop
23+
sql := format(
24+
'alter schema %I owner to %I;',
25+
r.nspname,
26+
new_owner
27+
);
28+
29+
raise debug'Execute SQL: %', sql;
30+
31+
execute sql;
32+
end loop;
33+
34+
-- Various DB objects
35+
-- c: composite type
36+
-- p: partitioned table
37+
-- i: index
38+
-- r: table
39+
-- v: view
40+
-- m: materialized view
41+
-- S: sequence
42+
for object_typein
43+
select
44+
unnest('{type,table,table,view,materialized view,sequence}'::text[]) type_name,
45+
unnest('{c,p,r,v,m,S}'::text[]) code
46+
loop
47+
for rin
48+
execute format(
49+
$sql$
50+
selectn.nspname,c.relname
51+
from pg_class c
52+
join pg_namespace non
53+
n.oid=c.relnamespace
54+
and notn.nspnamein ('pg_catalog','information_schema')
55+
andc.relkind= %L
56+
order byc.relname
57+
$sql$,
58+
object_type.code
59+
)
60+
loop
61+
sql := format(
62+
'alter %s %I.%I owner to %I;',
63+
object_type.type_name,
64+
r.nspname,
65+
r.relname,
66+
new_owner
67+
);
68+
69+
raise debug'Execute SQL: %', sql;
70+
71+
execute sql;
72+
end loop;
73+
end loop;
74+
75+
-- Functions, procedures
76+
for rin
77+
select
78+
p.proname,
79+
n.nspname,
80+
pg_catalog.pg_get_function_identity_arguments(p.oid)as args
81+
frompg_catalog.pg_namespaceas n
82+
joinpg_catalog.pg_procas ponp.pronamespace=n.oid
83+
where
84+
notn.nspnamein ('pg_catalog','information_schema')
85+
andp.proname not ilike'dblink%'-- We do not want dblink to be involved (exclusion)
86+
loop
87+
sql := format(
88+
'alter function %I.%I(%s) owner to %I',-- todo: check support CamelStyle r.args
89+
r.nspname,
90+
r.proname,
91+
r.args,
92+
new_owner
93+
);
94+
95+
raise debug'Execute SQL: %', sql;
96+
97+
execute sql;
98+
end loop;
99+
100+
-- Full text search dictionary
101+
-- TODO: text search configuration
102+
for rin
103+
select*
104+
frompg_catalog.pg_namespace n
105+
joinpg_catalog.pg_ts_dict dond.dictnamespace=n.oid
106+
where notn.nspnamein ('pg_catalog','information_schema')
107+
loop
108+
sql := format(
109+
'alter text search dictionary %I.%I owner to %I',
110+
r.nspname,
111+
r.dictname,
112+
new_owner
113+
);
114+
115+
raise debug'Execute SQL: %', sql;
116+
117+
execute sql;
118+
end loop;
119+
120+
-- Domains
121+
for rin
122+
select typname, nspname
123+
frompg_catalog.pg_type
124+
joinpg_catalog.pg_namespaceonpg_namespace.oid=pg_type.typnamespace
125+
where typtype='d'and not nspnamein ('pg_catalog','information_schema')
126+
loop
127+
sql := format(
128+
'alter domain %I.%I owner to %I',
129+
r.nspname,
130+
r.typname,
131+
new_owner
132+
);
133+
134+
raise debug'Execute SQL: %', sql;
135+
136+
execute sql;
137+
end loop;
138+
end
139+
$$;
140+
```
141+
142+
Do not forget to change the value of`new_owner`.
143+
144+
The query excludes schemas`pg_catalog` and`information_schema`, and it covers: schema objects, tables, views,
145+
materialized views, functions, text search dictionaries, and domains. Depending on your PG versions, there might be
146+
other objects you need to address. Adjust the code if/as needed.
147+
148+
To see the debug messages, change`client_min_messages` before running:
149+
150+
```sql
151+
set client_min_messages to debug;
152+
```
Lines changed: 122 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,122 @@
1+
Originally from:[tweet](https://twitter.com/samokhvalov/status/1739545313311133849),[LinkedIn post]().
2+
3+
---
4+
5+
#How to tune Linux parameters for OLTP Postgres
6+
7+
>I post a new PostgreSQL "howto" article every day. Join me in this
8+
>journey –[subscribe](https://twitter.com/samokhvalov/), provide feedback, share!
9+
10+
Here are general recommendations for basic tuning of Linux to run Postgres under heavy OLTP (web/mobile apps) workloads.
11+
Most of them are default settings used in[postgresql_cluster](https://github.com/vitabaks/postgresql_cluster).
12+
13+
Consider the parameters below as entry points for further study, and values provided as just rough tuning that is worth
14+
reviewing for a particular situation.
15+
16+
Most of the parameters can be changed in`sysctl.conf`. After changing it, this needs to be called:
17+
18+
```bash
19+
sysctl -p /etc/sysctl.conf
20+
```
21+
22+
Temporary change (taking`vm.swappiness` as example):
23+
24+
```bash
25+
sudo sysctl -w vm.swappiness=1
26+
```
27+
28+
or:
29+
30+
```bash
31+
echo 1| sudo tee /proc/sys/vm/swappiness
32+
```
33+
34+
##Memory management
35+
36+
1)`vm.overcommit_memory = 2`
37+
38+
Avoid memory overallocation to prevent OOM killer from affecting Postgres.
39+
40+
2)`vm.swappiness = 1`
41+
42+
Minimalistic swap, not fully switching it off.
43+
>💀 This is a controversial topic; I personally have used 0 here under
44+
heavy loads in mission-critical systems and taking my chances with the OOM killer; however, many experts suggest not
45+
turning it off completely and using a low value – 1 or 10.
46+
47+
**Good articles on this topic:**
48+
49+
-[Deep PostgreSQL Thoughts: The Linux Assassin](https://crunchydata.com/blog/deep-postgresql-thoughts-the-linux-assassin)
50+
(2021; k8s context) by[@josepheconway](https://twitter.com/josepheconway)
51+
52+
-[PostgreSQL load tuning on Red Hat Enterprise Linux](https://redhat.com/en/blog/postgresql-load-tuning-red-hat-enterprise-linux) (2022)
53+
54+
3)`vm.min_free_kbytes = 102400`
55+
56+
Ensure available memory for Postgres during memory allocation spikes.
57+
58+
4)`transparent_hugepage/enabled=never`,`transparent_hugepage/defrag=never`
59+
60+
Disable Transparent Huge Pages (THP) as they can induce latency and fragmentation not suitable for Postgres OLTP
61+
workloads. Disabling THP is generally recommended for OLTP systems (e.g., Oracle).
62+
63+
-[Ubuntu/Debian](https://stackoverflow.com/questions/44800633/how-to-disable-transparent-huge-pages-thp-in-ubuntu-16-04lts)
64+
-[Red Hat](https://access.redhat.com/solutions/46111)
65+
66+
##I/O Management
67+
68+
5)`vm.dirty_background_bytes = 67108864`
69+
70+
6)`vm.dirty_bytes = 536870912`
71+
72+
These ^ two are to tune[pdflush](https://lwn.net/Articles/326552/) to prevent IO lag spikes. See
73+
also:[PgCookbook - a PostgreSQL documentation project](https://github.com/grayhemp/pgcookbook/blob/master/database_server_configuration.md) by
74+
[@grayhemp](https://twitter.com/grayhemp).
75+
76+
##Network Configuration
77+
78+
>📝 note that below ipv4 settings are provided;
79+
>🎯**TODO:** ipv6 options
80+
81+
7)`net.ipv4.ip_local_port_range = 10000 65535`
82+
83+
Allows handling of more client connections.
84+
85+
8)`net.core.netdev_max_backlog = 10000`
86+
87+
Handles bursts of network traffic without packet loss.
88+
89+
9)`net.ipv4.tcp_max_syn_backlog = 8192`
90+
91+
Accommodates high levels of concurrent connection attempts.
92+
93+
10)`net.core.somaxconn = 65535`
94+
95+
Increases the limit for queued socket connections.
96+
97+
11)`net.ipv4.tcp_tw_reuse = 1`
98+
99+
Reduces connection setup time for high throughput OLTP applications.
100+
101+
##NUMA Configuration
102+
103+
12)`vm.zone_reclaim_mode = 0`
104+
105+
Avoids the performance impact of reclaiming memory across NUMA nodes for Postgres.
106+
107+
13)`kernel.numa_balancing = 0`
108+
109+
Disables automatic NUMA balancing to enhance CPU cache efficiency for Postgres.
110+
111+
14)`kernel.sched_autogroup_enabled = 0`
112+
113+
Improves process scheduling latency for Postgres.
114+
115+
##Filesystem and File Handling
116+
117+
15)`fs.file-max = 262144`
118+
119+
Maximum number of file handles that the Linux kernel can allocate. When running a database server like Postgres, having
120+
enough file descriptors is critical to handle numerous connections and files simultaneously.
121+
122+
>🎯**TODO:** review and adjust for various popular OSs

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp