pgexperts / flexible-freeze Goto Github PK

Looks like --exclude-table-in-database requires an argument or the script blows up:

Doesn't work:

Traceback (most recent call last):
  File "/usr/local/bin/flexible_freeze/scripts/flexible_freeze.py", line 134, in <module>
    for elem in args.exclude_table_in_database:
TypeError: 'NoneType' object is not iterable

Works fine:

/usr/bin/python2.7 /usr/local/bin/flexible_freeze/scripts/flexible_freeze.py --minutes 1 -H localhost --exclude-table-in-database this_is_not_a_real_database.this_is_not_a_real_table

Per http://www.postgresql.org/message-id/[email protected]

Add an --insert mode to flexible freeze, in order to update the visibility map for insert-only tables which don't otherwise get vacuumed.

Output activity stats to a JSON file or table documenting how many and what tables flexible freeze has updated.

Depends On: #4

This actually consists of two features:

• a touch file or other kill switch which would cause flexible-freeze to terminate at the end of the current operation.
• a command which would immediately kill flexible freeze and clean up its database connections.

Add --analyze mode to analyze only certain tables which otherwise might not get analyzed promptly, such as very large append-only tables, or very small tables. Would work on a simple time-limit basis.

Depends On: Issue #3

Move flexible freeze configuration to a ConfigObj file which defines all variables.

Depends On: #6, #5

Rewrite flexible freeze so that instead of using prescheduled low traffic periods, it can monitor for and detect prolonged low traffic periods and kick in then. The user would instead schedule known high traffic periods as blocked out periods.

This would be pretty tough to carry out, and might not end up being a good idea. If we get to this level, it's probably better to look at a PostgreSQL patch.

Depends On: #4

Currently we have an inclusion list for databases. It would be good to also have a table exclusion list, which would allow the user to exclude a list of tables which they know are regularly truncated/dropped. There would need to be a seperate list for each mode.

Depends On: #4, #6, probably others.

Add mode to flexible_freeze to opportunistically pg_repack severely bloated tables. This would be very useful, but suffers from a number of challenges:

• currently pg_repack is not designed to be called from another program. It's output depends on being read by a human on stderr. pg_repack would need an API, which might not be feasible.
• there's no safe way to cancel a pg_repack if the time limit runs out. So we'd need to estimate the time required for a repack before starting.
• since pg_repack will cancel competing transactions, do we really want to run it unattended?
• also, pg_repack can use up disk space, so we'd need a way to check available disk space and refuse to proceed if it's not at least (table_size * 2). However, a platform-agnostic way to check available disk space would be complicated.

Add integration with the pgagent tool/extension. Not sure what this would involve, exactly.

Depends On: Issue #4

Create flexible_freeze as a daemon service with several low traffic time windows defined in the config file.

This is the current tip of master:

/usr/local/bin/flexible_freeze.py: 10: /usr/local/bin/flexible_freeze.py: Flexible Freeze script for PostgreSQL databases
Version 0.5
(c) 2014 PostgreSQL Experts Inc.
Licensed under The PostgreSQL License

This script is designed for doing VACUUM FREEZE or VACUUM ANALYZE runs
on your database during known slow traffic periods.  If doing both
vacuum freezes and vacuum analyzes, do the freezes first.

Takes a timeout so that it wont: File name too long
/usr/local/bin/flexible_freeze.py: 11: /usr/local/bin/flexible_freeze.py: Note: not found
/usr/local/bin/flexible_freeze.py: 12: /usr/local/bin/flexible_freeze.py: may: not found
/usr/local/bin/flexible_freeze.py: 272: /usr/local/bin/flexible_freeze.py: Syntax error: Unterminated quoted string```

This needs some pythonification, including a class, a wrapper which sets paths, and a requirements.txt file.

I'm planning to add a --lock option that uses an advisory lock to prevent multiple copies from running at the same time.

I have the postgresql94-server, postgresql94-devel, postgresql94-libs, and postgresql94 packages installed from the official PostgreSQL repo.

I've tried to install the dependencies using: pip install -r requirements.txt and it fails with:

sudo pip install -r requirements.txt 
You are using pip version 7.1.0, however version 8.0.2 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
Collecting argparse==1.2.1 (from -r requirements.txt (line 1))
/usr/lib/python2.6/site-packages/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.
  InsecurePlatformWarning
  Using cached argparse-1.2.1.tar.gz
Collecting psycopg2==2.5.1 (from -r requirements.txt (line 2))
  Using cached psycopg2-2.5.1.tar.gz
    Complete output from command python setup.py egg_info:
    running egg_info
    creating pip-egg-info/psycopg2.egg-info
    writing pip-egg-info/psycopg2.egg-info/PKG-INFO
    writing top-level names to pip-egg-info/psycopg2.egg-info/top_level.txt
    writing dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txt
    writing manifest file 'pip-egg-info/psycopg2.egg-info/SOURCES.txt'
    warning: manifest_maker: standard file '-c' not found
    Error: pg_config executable not found.

    Please add the directory containing pg_config to the PATH
    or specify the full executable path with the option:

        python setup.py build_ext --pg-config /path/to/pg_config build ...

    or with the pg_config option in 'setup.cfg'.

    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-NJv2UJ/psycopg2

I've tried updating the path to explicitly include pg_config: export PATH=$PATH:/usr/pgsql-9.4/bin/ and even tried alias pg_config=/usr/pgsql-9.4/bin/pg_config to no avail.

Does this work with Python 2.6 on CentOS 6?

I'm planning to add a --jobs option to run multiple freezes in parallel.

Depends On: Issue #6

Add configuration option and method for flexible freeze to notice that the database has come under heavy load and abort. Possible checks might include uptime load and/or number of concurrent active queries.

Add keyboard interrupt capture so that flexible-freeze will shut down if the user aborts it from the command line.

This is separate from the kill switch, which is for daemonized FF.

Depends On: Issue #3

Parallelize flexible freeze so that it can vacuum a user-specified number of tables at once.

If your database has multiple schemas, and the base query detects one of these tables needs to be vacuumed, it fails because the schema-qualified table name is being wrapped in double quotes.

The failure looks like the following:

2022-08-09 14:20:52 UTC: VACUUM FREEZE ANALYZE "schema.table" in database dbname
2022-08-09 14:20:52 UTC: VACUUMing schema.table failed.
2022-08-09 14:20:52 UTC: relation "schema.table" does not exist

Depends On: #3, #4, #5 and #6

Since flexible freeze can be run from a dedicated server instead of from the database server, consider having a version of flexible freeze which allows per-target-server configuration for multiple servers. This might be infeasable since we're already having four modes, and the intersection of modes and servers could be quite painfully complex.