Skip to content

Instantly share code, notes, and snippets.

View pg_13_rewind_and_back
postgres@db:~/playground/rewinddemo$ initdb -D primary
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
View cascading_replication.sh
i just did a quick
primary -> replica -> cascading_replica setup and then failed over
from primary to replica.
cascading replica was getting all changes from replica(new primary) just fine.
i have pg14beta installed btw.
initdb -D primary
The files belonging to this database system will be owned by user "postgres".
View table_type_example.sql
-- https://github.com/postgres/postgres/blob/master/src/test/regress/sql/typed_table.sql
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create function myfunc(anyarray) returns void as $$
test$# begin
test$# insert into persons select * from unnest($1);
test$# end; $$ language plpgsql;
CREATE FUNCTION
View pg_core_dump_ubuntu_example
# i modified https://github.com/postgres/postgres/blob/7c44c46deb495a2f3861f402d7f2109263e3d50a/src/backend/access/common/heaptuple.c#L94
to
if (att->atthasmissing) -> if (! att->atthasmissing)
# https://www.highgo.ca/2019/10/03/trace-query-processing-internals-with-debugger/
recompiled and make install
./configure --prefix /home/postgres/example/pgsql --enable-debug 2>&1 >/dev/null
make install
View select_grant_pg_.txt
postgres@db:~$ dropdb example
postgres@db:~$
postgres@db:~$ createdb example
postgres@db:~$ psql example
psql (14beta1)
Type "help" for help.
example=# create schema example;
CREATE SCHEMA
example=# create table example.t(id bigserial);
View coredump.txt
https://ngelinux.com/how-to-enable-core-dump-collection-in-linux-for-every-user/#:~:text=%20Enabling%20Core%20dump%20in%20Linux.%20%201,core%20unlimited%E2%80%9D%20to%20enable%20the%20core...%20More%20
postgres@db:~/playground/logical_replication$ ulimit -a
core file size (blocks, -c) 0
as root
printf "* soft core unlimited\n" >> /etc/security/limits.conf
postgres@db:~/playground/logical_replication$ sudo vim /etc/security/limits.conf
@cabecada
cabecada / view_with_window_agg.sql
Last active Jun 27, 2021
view_with_window_aggregation
View view_with_window_agg.sql
postgres=# table t;
id | value
----+-------
(0 rows)
postgres=# insert into t select x, x from generate_series(1, 100) x order by random();
INSERT 0 100
postgres=# create view myview as select id, value, row_number() over (order by id) from t;
CREATE VIEW
postgres=# analyze t;
@cabecada
cabecada / lr.sh
Last active Jun 25, 2021
logical_rep_demo_on_same_server
View lr.sh
option 2, create pub sub on the same server
from the docs,
Creating a subscription that connects to the same database cluster
(for example, to replicate between databases in the same cluster or to replicate within the same database)
will only succeed if the replication slot is not created as part of the same command.
Otherwise, the CREATE SUBSCRIPTION call will hang. To make this work, create the
replication slot separately (using the function pg_create_logical_replication_slot with the plugin name pgoutput)
and create the subscription using the parameter create_slot = false.
This is an implementation restriction that might be lifted in a future release
View pg_on_ramfs.txt
https://stackpointer.io/unix/linux-create-ram-disk-filesystem/438/
root@db:~# rm -rf /mnt/ramdisk
root@db:~# mknod -m 660 /dev/ram0 b 1 0
root@db:~# chown postgres:disk /dev/ram0
root@db:~# mkdir -p /mnt/ramdisk
root@db:~# chown -R postgres /mnt/ramdisk
root@db:~# mount -t ramfs -o size=128M,maxsize=128M /dev/ram0 /mnt/ramdisk
root@db:~# ls -ld /mnt/ramdisk
drwxr-xr-x 2 root root 0 Jun 22 18:52 /mnt/ramdisk
View pg_large_object_test.sql
fallocate -l 20G example.fallocate
postgres@db:~$ psql test
psql (14beta1)
Type "help" for help.
test=# CREATE TABLE image (
id integer,
name text,
picture oid