Thread: Stripping out slony after / before / during pg_restore?

Hi people,

I'm setting us up a separate staging / test server and I want to read in a pg_dump of our current origin stripping out
allthe slony stuff. 

I was thinking this could serve two purposes a) test out backups restore properly and b) provide us with us with the
staging/ test server 

What's the best way to remove all the slony bits?

I was thinking read in the dump, then use uninstall node - but I'd rather not have to run the slon daemons.

Or should I just leave all the slony stuff in there... would it cause us any problems? There'd be no slons running and
thenext night it's all wiped and restored again... 

Anyone got any ideas? Anyone got something similar already?

Cheers
Glyn


      __________________________________________________________
Sent from Yahoo! Mail.
A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html

Glyn Astill wrote:
> Hi people,
>
> I'm setting us up a separate staging / test server and I want to read
> in a pg_dump of our current origin stripping out all the slony stuff.
>
> I was thinking this could serve two purposes a) test out backups
> restore properly and b) provide us with us with the staging / test
> server
>
> What's the best way to remove all the slony bits?

Well, you can always just drop the slony schema (with a cascade) - that
should do it.

--
   Richard Huxton

Thanks guys, that's exactly what I wanted.

----- Original Message ----
> From: Richard Huxton <dev@archonet.com>
> To: Glyn Astill <glynastill@yahoo.co.uk>
> Cc: slony1-general@lists.slony.info; pgsql-general@postgresql.org
> Sent: Tuesday, 13 May, 2008 1:34:18 PM
> Subject: Re: [GENERAL] Stripping out slony after / before / during pg_restore?
>
> Glyn Astill wrote:
> > Hi people,
> >
> > I'm setting us up a separate staging / test server and I want to read
> > in a pg_dump of our current origin stripping out all the slony stuff.
> >
> > I was thinking this could serve two purposes a) test out backups
> > restore properly and b) provide us with us with the staging / test
> > server
> >
> > What's the best way to remove all the slony bits?
>
> Well, you can always just drop the slony schema (with a cascade) - that
> should do it.
>
> --
>    Richard Huxton



      __________________________________________________________
Sent from Yahoo! Mail.
A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html

Here's how you do it on restore step from a pg_dump in -Fc format.

pg_restore -l dumpfile > list
edit the file "list" to remove references to slony objects
pg_restore -L list <other options you want> dumpfile

I have to do a multicolumn self-join to find the median. I am using postgres
8.2.  How do I force postgres to use an index scan?  I have a multicolumn
index but postgres is not using it.

Query
------------------------------------
explain select e.time
from page_view e, page_view d
where e.test_run_id = d.test_run_id and e.web_resource_id =
d.web_resource_id
and e.web_resource_id = 3961 and e.test_run_id = 2

1 index
-----------------------------------------------------------------------
create index page_view_page_idx ON page_view(test_run_id, web_resource_id);

primar key

--------------------------------------

(test_run_id,page_view_id)

plan

--------------------------------------------------------------

"Nested Loop  (cost=127.58..13592618.11 rows=33489369 width=8)"
"  ->  Seq Scan on page_view e  (cost=0.00..3291.26 rows=5787 width=16)"
"        Filter: ((web_resource_id = 3961) AND (test_run_id = 2))"
"  ->  Bitmap Heap Scan on page_view d  (cost=127.58..2290.38 rows=5787
width=8)"
"        Recheck Cond: ((3961 = web_resource_id) AND (2 = test_run_id))"
"        ->  Bitmap Index Scan on page_view_page_idx  (cost=0.00..126.13
rows=5787 width=0)"
"              Index Cond: ((3961 = web_resource_id) AND (2 = test_




Steve

On Tue, May 13, 2008 at 9:27 AM, Stephen Ince <since@opendemand.com> wrote:
> I have to do a multicolumn self-join to find the median. I am using postgres
> 8.2.  How do I force postgres to use an index scan?  I have a multicolumn
> index but postgres is not using it.

While it's quite acceptable to force index usage during testing, it's
a bad idea to do so on a production server unless you have to.

From the psql command line do a "show all;" and look for the
enable_xxx settings.  Those allow you to do things like turn off seq
scans (actually it makes them really expensive so that they don't get
chosen, usually).

enable_indexscan
enable_seqscan

Are the two to look for.

> Query
> ------------------------------------
> explain select e.time
> from page_view e, page_view d
> where e.test_run_id = d.test_run_id and e.web_resource_id =
> d.web_resource_id
> and e.web_resource_id = 3961 and e.test_run_id = 2

I'd rewrite this as:

explain analyze
select e.time
from page_view e
join page_view d
on ((e.test_run_id, e.web_resource_id) = (d.test_run_id,d.web_resource_id))
where e.web_resource_id = 3961 and e.test_run_id = 2

Which makes it more readable.  Don't know if that syntax makes the
planner smarter or not.  Note the addition of analyze up there.  that
will help you know what's actually happening.

On May 13, 2008, at 5:27 PM, Stephen Ince wrote:

> I have to do a multicolumn self-join to find the median. I am using
> postgres 8.2.  How do I force postgres to use an index scan?  I
> have a multicolumn index but postgres is not using it.
>
> Query
> ------------------------------------
> explain select e.time
> from page_view e, page_view d
> where e.test_run_id = d.test_run_id and e.web_resource_id =
> d.web_resource_id
> and e.web_resource_id = 3961 and e.test_run_id = 2

How does that get you the median? It just gives you all the records
in page_view e with the same (test_run_id, web_resource_id) tuple.
AFAICS you could leave out the entire self-join and get the same
results. No wonder it uses a seqscan...

Personally to find the median I'd use a scrolling cursor. Execute
your query, ordered by time, using a scrolling cursor; scroll to the
last row; determine the row_count from that; scroll back to half way
your result set now that you know what size it is.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,482bd864927661472788033!

Richard Huxton <dev@archonet.com> writes:
> Glyn Astill wrote:
>> Hi people,
>>
>> I'm setting us up a separate staging / test server and I want to read
>> in a pg_dump of our current origin stripping out all the slony stuff.
>>
>> I was thinking this could serve two purposes a) test out backups
>> restore properly and b) provide us with us with the staging / test
>> server
>>
>> What's the best way to remove all the slony bits?
>
> Well, you can always just drop the slony schema (with a cascade) -
> that should do it.

Not quite.  There are two things that *doesn't* hit:

 a) If there were any tables where a Slony-I key column got added,
    DROP SCHEMA CASCADE doesn't trim that column out, which can
    cause some minor heartburn.  If you don't use TABLE ADD KEY,
    then this isn't an issue.

 b) In versions of Slony-I before 2.0, stripping out the schema on a
    subscriber will leave triggers in a hidden state.

You want to look into UNINSTALL NODE, which does the full cleanup.
--
(format nil "~S@~S" "cbbrowne" "linuxfinances.info")
http://linuxfinances.info/info/languages.html
REALITY is a crutch for people who can't face ITS.

>>> I'm setting us up a separate staging / test server and I want to read
>>> in a pg_dump of our current origin stripping out all the slony stuff.
>>>
>>> I was thinking this could serve two purposes a) test out backups
>>> restore properly and b) provide us with us with the staging / test
>>> server
>>>
>>> What's the best way to remove all the slony bits?
>>
>> Well, you can always just drop the slony schema (with a cascade) -
>> that should do it.
>
> Not quite.  There are two things that *doesn't* hit:

So what was the final recommended process for building a stand alone
database from a pg_dump of a replicated node?

pg_dump --oids --format=c --file=master.dump master_db
createdb staging_db
pg_restore -d staging_db master.dump
slonik << EOM
cluster name = sl;
node 1 admin conninfo = 'dbname=staging_db user=slony';
uninstall node (id = 1);
EOM

This process dies on the last step with:

<stdin>:3: PGRES_FATAL_ERROR select "_sl".uninstallNode();  - ERROR:
Slony-I: alterTableRestore(): Table with id 1 not found
CONTEXT:  SQL statement "SELECT  "_sl".alterTableRestore( $1 )"
PL/pgSQL function "uninstallnode" line 14 at PERFORM
Failed to exec uninstallNode() for node 1

So if I'm reading this thread correctly, the alternative is 'DROP _sl
CASCADE;', which doesn't do a full cleanup. Is there no supported
disaster recovery procedure?

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/

On Mon, Oct 13, 2008 at 5:05 PM, Stuart Bishop <stuart@stuartbishop.net> wrote:

> So what was the final recommended process for building a stand alone
> database from a pg_dump of a replicated node?

> So if I'm reading this thread correctly, the alternative is 'DROP _sl
> CASCADE;', which doesn't do a full cleanup. Is there no supported
> disaster recovery procedure?

So to (hopefully) answer my own question, the following seems to Do
The Right Thing™:

pg_dump --oids --format=c --file=master.dump master_db
createdb staging_db
pg_restore -d staging_db master.dump
slonik << EOM
cluster name = sl;
node 1 admin conninfo = 'dbname=staging_db user=slony';
repair config (set id = 1, event node = 1, execute only on = 1);
repair config (set id = 2, event node = 1, execute only on = 1);
uninstall node (id = 1);
EOM

Can anyone who actually knows what they are doing confirm or ridicule
this recipe?

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/