Hi everyone!
Trying to make VACUUM FREEZE on PG instance and keep getting this error:
2016-03-18 05:56:51 UTC 46750 WARNING: oldest xmin is far in the past
2016-03-18 05:56:51 UTC 46750 HINT: Close open transactions soon to
avoid wraparound problems.
2016-03-18 05:56:51 UTC 46750 DEBUG: transaction ID wrap limit is
2654342112, limited by database with OID 1
2016-03-18 05:56:51 UTC 46750 DEBUG: MultiXactId wrap limit is
2147483648, limited by database with OID 12451
Also "age" and "relfrozenxid" doesnt't change.
I will show what I'm trying to do step by step:
Executing this command:
SELECT
pg_namespace.nspname
,c.relname AS relname
--,c.oid::regclass as table_name
,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
,c.relfrozenxid
,t.relfrozenxid
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
LEFT JOIN
pg_namespace
ON pg_namespace.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY
age desc
,1,2;
Output looks like this:
nspname
relname
age
relfrozenxid
relfrozenxid
public
action_flows
543567979
506858465
506858465
public
advertiser_requests
543567979
506858465
506858465
public
authtokens
543567979
506858465
506858465
public
blacklist
543567979
506858465
506858465
public
blog_categories
543567979
506858465
506858465
public
blog_posts
543567979
506858465
506858465
public
bp_service_codes
543567979
506858465
506858465
public
browsers
543567979
506858465
506858465
Then I'm doing: VACUUM FREEZE; and nothing happens, I only get debug and
warning messages as I mentioned above.
Settings on server:
name setting unit
autovacuum on
autovacuum_analyze_scale_factor 0.1
autovacuum_max_workers 20
autovacuum_vacuum_cost_delay 0 ms
autovacuum_vacuum_cost_limit 200
autovacuum_vacuum_scale_factor 0.2
bgwriter_delay 200 ms
checkpoint_completion_target 0.9
checkpoint_segments 128
checkpoint_timeout 1800 s
client_encoding UTF8
client_min_messages debug1
commit_delay 5000
commit_siblings 15
DateStyle ISO, MDY
deadlock_timeout 1000 ms
debug_pretty_print on
default_statistics_target 100
default_text_search_config pg_catalog.english
dynamic_shared_memory_type posix
effective_cache_size 12582912 8kB
extra_float_digits 3
fsync on
full_page_writes off
lc_messages en_US.UTF-8
lc_monetary en_US.UTF-8
lc_numeric en_US.UTF-8
lc_time en_US.UTF-8
listen_addresses *
log_autovacuum_min_duration 1000 ms
log_checkpoints on
log_destination stderr
log_directory /home/pgsql/data/pg_log
log_filename postgresql-%a.log
log_line_prefix %t %h %u %p
log_lock_waits on
log_min_duration_statement 1000 ms
log_min_error_statement debug1
log_min_messages debug1
log_rotation_age 1440 min
log_rotation_size 0 kB
log_statement none
log_timezone UTC
log_truncate_on_rotation on
logging_collector on
maintenance_work_mem 2097152 kB
max_connections 800
max_prepared_transactions 10
max_replication_slots 1
max_stack_depth 2048 kB
max_wal_senders 3
port 9125
random_page_cost 1.2
search_path public
seq_page_cost 1
shared_buffers 6553600 8kB
synchronous_commit off
temp_buffers 16384 8kB
TimeZone Europe/Moscow
track_counts on
update_process_title off
vacuum_cost_delay 1 ms
vacuum_freeze_min_age 75000000
vacuum_freeze_table_age 200000000
vacuum_multixact_freeze_min_age 5000000
vacuum_multixact_freeze_table_age 150000000
wal_buffers 2048 8kB
wal_keep_segments 128
wal_level hot_standby
work_mem 65536 kB
Also:
select txid_current(); - 5345750425
select xmin from stats_y2016_m3 order by ts_spawn desc limit 1; - 1050801875
why such difference?
