⚠️ Recommends support request⚠️
No support request
export PGPASSWORD="<password>"
host="<cluster name>.postgres.cosmos.azure.com"
user="citus"
db="citus"
schema="public"
Optional: create test table
create table public.test (name varchar)
insert into public.test (name) values ('test')
pg_dump \
--format=plain \
--no-owner \
--schema-only \
--file=/mnt/storage/$schema.sql \
--schema=$schema \
postgres://$user@$host:5432/$db
pg_dump \
--format=custom \
--no-owner \
--data-only \
--file=/mnt/storage/${schema}data.dump \
--schema=$schema \
postgres://$user@$host:5432/$db
Dump slow?
High dead_pct
(dead tubles percentage) indicates the table may need to be vacuumed:
select
schemaname,
relname as table_name,
n_dead_tup,
n_live_tup,
round(n_dead_tup::float/n_live_tup::float*100) dead_pct,
autovacuum_count,
last_vacuum,
last_autovacuum,
last_autoanalyze,
last_analyze
from pg_stat_all_tables
where n_live_tup >0
order by round(n_dead_tup::float/n_live_tup::float*100) desc;
Vacuum:
vacuum(analyze, verbose) <table_name>
For Azure, create a Container Instance with a mounted storage account file share. Install a version of PostgreSQL that matches the target server. Open the IP of the container instance on the PostgreSQL server. Run pg_dump
on the container instance with the file targeting the storage account mount.
Optional: drop test table
drop table public.test;
psql \
-h $host \
-d $db \
-U $user \
-f /mnt/storage/$schema.sql
- Run your create_distributed_table and create_reference_table statements. If you get an error about foreign keys, it’s generally due to the order of operations. Drop foreign keys before distributing tables and then re-add them.
- Put the application into maintenance mode, and disable any other writes to the old database.
pg_restore \
--host=$host \
--dbname=$db \
--username=$user \
/mnt/storage/${schema}data.dump
Optional: select test table
select * from public.test limit 1;