Postgresql Queries
Postgres showing how you can determine the size of a table (in MB), with or without indexes, as well determining database size
select n.nspname as schema_name, c.relname as table_name, c.reltuples as row_count from pg_class c join pg_namespace n on n.oid = c.relnamespace where relkind = 'r' and nspname != 'pg_catalog' order by 1, 2;
Query to list the row counts of your tables, as well as the size on disk, I shared with you the below query:
select n.nspname as schema_name, c.relname as table_name, c.reltuples::int as row_count, pg_size_pretty(pg_relation_size(c.oid)) as table_size from pg_class c join pg_namespace n on n.oid = c.relnamespace where relkind ='r' and nspname != 'pg_catalog' order by 3 desc, 2;
If your table statistics are not up to date, you run the risk of getting only an estimated row count from this query, as it is querying table information from pg_catalog.
please be sure to add a comma separated list of schemas in place of <your schemas>.
do $$
declare t record;
declare r record;
begin
for t in select n.nspname, c.relname from pg_class c join pg_namespace n on n.oid = c.relnamespace where c.relkind = 'r' and n.nspname in (<your schemas>)
order by 1, 2
loop
raise notice 'Table: %.%', t.nspname, t.relname;
execute 'select count(*) as rownum from '||t.nspname||'.'||t.relname||';' into r;
raise notice 'Number
of rows is %', r.rownum;
end loop;
end $$;
As an example, I specified my list of schemas as follows: where c.relkind = 'r' and n.nspname in ('public', 'myschema', 'jimmy') order by 1, 2
Please be sure to test this script in a non-prod environment before using it in a production environment.
Disk Usage - PostgreSQL Wiki - https://wiki.postgresql.org/wiki/Disk_Usage
----------------------------------------------------------------------------------------------------------------------------------------------
Below to remove the Foreign Keys in PostgreSQL Target. Also the second is the Query to take them back
create table if not exists dropped_foreign_keys (
seq bigserial primary key,
sql text
);
do $$ declare t record;
begin
for t in select conrelid::regclass::varchar table_name, conname constraint_name, pg_catalog.pg_get_constraintdef(r.oid, true) constraint_definition
from pg_catalog.pg_constraint r where r.contype = 'f' --current schema only:
and r.connamespace = (select n.oid from pg_namespace n where n.nspname = current_schema())
loop
insert into dropped_foreign_keys (sql) values ( format('alter table %s add constraint %s %s', t.table_name,
t.constraint_name, t.constraint_definition));
execute format('alter table %s drop constraint %s', t.table_name, t.constraint_name);
end
loop;
end $$;
The code above creates a table named "dropped_foreign_keys". The code will get foreign Key information from "pg_catalog.pg_constraint".
The code will iterate to formulate the "ALTER TABLE ....." and insert into "dropped_foreign_keys". It then drop that Key until all the foreign key are done.
To retain the foreign Keys, you read from "dropped_foreign_keys" table and get the stored statement from the field "sql". "sql" contain the executable statement to re-create the the foreign keys. The code below will do that piece of work.
Note: After the full-load is complete, please run this query to recreate foreign keys
==================================================================================
do $$ declare t record;
begin
-- order by seq for easier troubleshooting when data does not satisfy FKs
for t in select * from dropped_foreign_keys order by seq loop
execute t.sql;
delete from dropped_foreign_keys where seq = t.seq;
end loop;
end $$;
---------------------------------------------------------------------------------------------------------------------------
Drop foreign key constraints, secondary indexes and disable triggers
Execute the following script to generate SQL for disabling foreign key constraints.
--Use the below SQL to generate the scripts to drop foreign keys
select 'alter table '||con.conrelid::regclass||' drop constraint '||con.conname||';' as drop
from pg_constraint con
join pg_namespace ns on con.connamespace = ns.oid
where ns.nspname = 'Schema_Name'
and con.contype = 'f';
Execute the following script to generate SQL for disabling foreign key constraints.
--Use the below SQL to generate the scripts to disable triggers
select 'alter table '||trigger_schema||'.'||event_object_table||' disable trigger '||trigger_name||';' as disable
from information_schema.triggers
where trigger_schema = 'hr';
Enable foreign keys, triggers and secondary indexes
--Use the below SQL to generate the script to create foreign keys
select 'alter table '||con.conrelid::regclass||' add constraint '||con.conname||' '||pg_get_constraintdef(con.oid)||';' as create
from pg_constraint con
join pg_namespace ns on con.connamespace = ns.oid
where ns.nspname = 'schema_name'
and con.contype = 'f';
Execute the following script to generate SQL for enabling triggers
--Use the below SQL to generate the scripts enable triggers
select 'alter table '||trigger_schema||'.'||event_object_table||' enable trigger '||trigger_name||';' as enable
from information_schema.triggers
where trigger_schema = 'schema_name';