Skip to content

Instantly share code, notes, and snippets.

@cabecada
cabecada / table_delete_using_oid_from_pg_class_restore
Last active Jul 24, 2021
table_delete_using_oid_from_pg_class_restore
View table_delete_using_oid_from_pg_class_restore
postgres@db:~/playground/demo$ pg_ctl -D data -l logfile start
waiting for server to start.... done
server started
postgres@db:~/github$ cd pagila
postgres@db:~/github/pagila$ ls -lrt
total 8292
-rw-rw-r-- 1 postgres postgres 5304 Jul 7 18:21 README.md
-rw-rw-r-- 1 postgres postgres 1100 Jul 7 18:21 LICENSE.txt
-rw-rw-r-- 1 postgres postgres 3034559 Jul 7 18:21 pagila-data.sql
-rw-rw-r-- 1 postgres postgres 52458 Jul 7 18:21 pagila-schema.sql
@cabecada
cabecada / postgres_composite_index
Created Jul 18, 2021
postgres_composite_index
View postgres_composite_index
\d+ dates
Table "public.dates"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------------+---------+-----------+----------+-----------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('dates_id_seq'::regclass) | plain | | |
date | date | | | | plain | | |
epoch | bigint | | | | plain | | |
day_suffix | text | | | | extended | pglz | |
day_name | text | | | |
@cabecada
cabecada / gist:cbc7506ba9ec0ae024d3284db57ba746
Created Jul 13, 2021
logical_replication_to_diff_dbs_via_schemas
View gist:cbc7506ba9ec0ae024d3284db57ba746
setup:
original server has schema db1,db2
goal: create new servers db1 and db2 and move over schema db1 to server db1 and schema db2 to server db2
we make use of logical replication to publish INS,UPD,DEL changes from original server to respective schema servers.
https://www.postgresql.org/docs/current/logical-replication-restrictions.html
https://www.postgresql.org/docs/current/logical-replication.html
server mainserver db1server db2server
schema db1 -> db1
View reattach_replica_to_primary.txt
##create a db
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".
View gist:f9d6f07857ba4041223667e9abe40b63
cat test_part.sql
drop extension if exists pg_partman;
drop schema if exists partman cascade;
drop table if exists public.t cascade;
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
create table t(id int, col1 timestamp, primary key(id, col1)) partition by range(col1);
SELECT partman.create_parent( p_parent_table => 'public.t',
p_control => 'col1',
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);