Postgresql – Rsync’ed postgres databases won’t allow log-on, gets “cache lookup failed for relation” error

I do not have all information for pointing to the source of your problem, but I think you should create your postgresql cluster in a different way. Instead of using the postgresql generic command pg_ctl, you should use the Debian/Ubuntu provided command pg_createcluster. This way you will have a working setup that survives reboot, you may specify your data directory and your log directory, you may also specify the start policy in order to activate it automatically on boot. And you may use all cluster commands: pg_lsclusters, pg_upgradecluster, and so on.

Every cluster uses three directories:

• /etc/postgresql/8.4/CLUSTERNAME, for its configuration
• your data directory, for all your data and WAL (default /var/lib/postgres/8.4/CLUSTERNAME)
• your log directory, for your logs (default /var/log/postgresql)

The widely used tool is the SQL command EXPLAIN ANALYZE, possibly with more options for more details in the answer. That outputs the query plan with the planner estimates plus actual execution times.

Why would you want to clear the cache? The generally more likely use case is that the cache is populated. If you still want to go that route, here is a related answer on SO.

Not resetting the cache, here are two simple ways to test with many iterations:

Simple UDF

EXPLAIN ANALYZE
SELECT f_myfunc(g) FROM generate_series (1,1000) AS t(g);

Or with random input - random numbers between 0 and 5000 in the example:

EXPLAIN ANALYZE
SELECT f_myfunc((random()*5000)::int) FROM generate_series (1,1000) AS t(g);

Or with a real life table:

EXPLAIN ANALYZE
SELECT f_myfunc(my_column) FROM my_tbl;   -- LIMIT n

More complex functions / queries

CREATE FUNCTION f_test(ct int, sql text) RETURNS void AS
$func$
DECLARE
   i int;
BEGIN

FOR i IN 1 .. $1 LOOP
    EXECUTE sql;  -- not safe against SQLi!
END LOOP;

END
$func$ LANGUAGE plpgsql

Call:

EXPLAIN ANALYZE
SELECT f_test(100, $x$SELECT * from MADLIB.gp('mock3', '{x1, x2, x3}', '{y1}', 100,20, 3)$x$

Careful: The query is actually executed!
Careful: Not fit for public use. Possible SQL injection.

Again, you can use random parameters if needed. Possibly with the USING clause of EXECUTE.