Doubt with Image fields in Postgres

Asked

Viewed 55 times

0

I used the sql below to view the size of all tables of the database, it return me a total of 98 MB, more as the database has images that are recorded as binary and the same are not in a table, I would like to make a backup of the database without these images,there is some 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');

Tables with Images: inserir a descrição da imagem aqui

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 database backup

  • database has binary images, and are not in a table ? how so ? are where ?

  • The database has recorded images, but these images do not have a table, so much that when making a query using the sql above, I have a database with 98 MB, when generating a backup gets 4 GB, because it has images in a tablespace table, I want a backup only with tables without image.

  • and how is performing the backup ?

  • I’m using the visual option of Postgres

  • in that reply: https://answall.com/a/250074/69359 I use pg_dump to back up. There is a parameter --no-tablespaces which may be useful to you. Complete documentation: https://www.postgresql.org/docs/9.1/static/app-pgdump.html

  • Generated the backup of the database, I will test the restore, I appreciate the help

  • The final file was with 10GB, so it did not work

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.