I used the sql down to view the size of all the tables in the database, it returned me a total of 98 MB, but since the database has images that are written as Binary and they are not in a table, I would like to do a backup of the bank without these images, is there any way to remove these images?
Thanks for the help
SELECT
table_name,
pg_size_pretty(table_size) || ' (' || CASE WHEN total_size = 0 THEN 0.00 ELSE round(table_size * 100 / total_size) END || ' %)' AS table_size,
pg_size_pretty(indexes_size) || ' (' || CASE WHEN total_size = 0 THEN 0.00 ELSE round(indexes_size * 100 / total_size) END || ' %)' AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
(SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
) AS all_tables
ORDER BY total_size DESC)
UNION ALL
(SELECT
'TOTAL',
sum(pg_table_size(table_name)) AS table_size,
sum(pg_indexes_size(table_name)) AS indexes_size,
sum(pg_total_relation_size(table_name)) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
--Encontrando as tabelas que referenciam Large Objects
SELECT nspname AS esquema, relname AS tabela, attname AS coluna
FROM
pg_type t
JOIN pg_attribute a ON a.atttypid = t.oid
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
typname IN ('oid','lo') AND
attname NOT IN ('oid', 'tableoid') AND
nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
Proposed Solution:
1 - I accessed the CMD
2 - C: \ PostgreSQL96 \ bin \ pg_dump -h localhost -p 5432 --no-tablespaces -U postgres --inserts -c -f C: \ bkp \ backup_ezitus.dump ezitus-prod
3 - Asked for password
4 - Generated backup of the bank