pg_tools.sh

#backup table
pg_dump -h hostname -d <database_name> -t <table_name> > file.sql
pg_dump --no-owner -d <database_name> -t <table_name> > file.sql

#backup table only data
pg_dump --data-only -h hostname  -d <database_name> -t <table_name> > file.sql

#backup all data
pg_dump --dbname=authservice --data-only --file=/path/to/file.sql --username= --host= --port=5432


#backup with ddl
pg_dump --dbname=authservice --file=/path/to/file.sql --username= --host= --port=5432

other options 
--if-exists 
--clean 
--create 
--inserts
--inserts-columns

#import backup table 
psql -h hostname -d databasename -U username t table -f file.sql


tunning 
select relpages, reltuples from pg_class  WHERE  relname = 'table';

select name, setting from pg_settings where name in ('seq_page_cost', 'cpu_tuple_cost');


##copy csv with progress
pv /path/data.csv | psql -c "COPY TABLE FROM STDIN DELIMITER ','
CSV HEADER;"

## copy csv url with query
COPY table(columns...,) FROM PROGRAM 'curl https://data.csv';

## copy csv url with psql
\COPY table(columns...,) FROM PROGRAM 'curl https://data.csv';

## export data psql 
\copy table_name to 'filename.csv' csv header

## export with query
COPY table_name TO 'file_name.csv' DELIMITER ',' CSV HEADER;


## command external 
\ ! echo "table" > value
\set variable `cat value`
select * from :'variable'

## check if extension exists

select pg_get_functiondef(to_regproc('gen_random_uuid'));