Hi,
I would like to stop the postmaster every night and run
vacuum
pg_dump
reindex
in the stand alone backend.
Vacuum and reindex seem to be quite easy, as I can setup a small script
with both commands. But what about pg_dump. That seems "somewhat" more
complex.
TIA
Ulrich
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend 7 4717
Hi,
Citing Ulrich Wisser <ul***********@ relevanttraffic .se>: I would like to stop the postmaster every night and run
vacuum pg_dump reindex
in the stand alone backend.
Vacuum and reindex seem to be quite easy, as I can setup a small script with both commands. But what about pg_dump. That seems "somewhat" more complex.
What exactly is your problem about putting pg_dump in a (shell)script as
well?
In the simplest case you could use something like:
#!/bin/sh
psql --command vacuum your_database
cd /somewhere/with/write/access
pg_dump your_database > dump_`date %Y%m%d%H%M%S`.b ak
psql --command 'reindex whatever_you_wa nt_to_reindex' your_database
which will vacuum, dump to a file named dump_timewhendu mpoccured.bak and
reindex whatever_you_wa nt_to_reindex. All kinds of stuff could be added
(like mailing command output to you, loading the backup up to another
machine for storage etc. etc.), but above script does basically what
you were asking for.
Regards,
Daniel
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
Daniel Martini wrote: Hi,
Citing Ulrich Wisser <ul***********@ relevanttraffic .se>:
I would like to stop the postmaster every night and run
vacuum pg_dump reindex
in the stand alone backend.
Vacuum and reindex seem to be quite easy, as I can setup a small script with both commands. But what about pg_dump. That seems "somewhat" more complex.
What exactly is your problem about putting pg_dump in a (shell)script as well?
psql will not work without postmaster running. But if I let postmaster
run some other tools will connect to it and I can't get exclusive locks
on all tables for "vacuum full" and "reindex". With vacuum and reindex
I can do
#!/bin/bash
postgres -d /var/lib/pgsql/data -U xxx -O -P mydb < SQL
REINDEX DATABASE mydb
REINDEX TABLE t1 FORCE
REINDEX TABLE t2
....
REINDEX TABLE tn
VACUUM FULL VERBOSE ANALYZE
<<SQL
which will speed up my database on a daily basis at least by factor 5.
For security reasons I would also like to take a backup and I guess it
will be much faster in the stand alone backend.
Ulrich
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Ulrich Wisser wrote: Hi,
I would like to stop the postmaster every night and run
vacuum pg_dump reindex
in the stand alone backend.
Vacuum and reindex seem to be quite easy, as I can setup a small script with both commands. But what about pg_dump. That seems "somewhat" more complex.
Explain what exactly you are trying to do, why do you have to stop
the postmaster ? If you request is due only to forbid the access then
you can replace the pg_hba.conf with a "void" one and replace it again
at the end of operations.
BTW vacuum, pg_dump, reindex are operations that can be performed
with the server up and running.
Regards
Gaetano Mendola
Hi, I would like to stop the postmaster every night and run
vacuum pg_dump reindex
in the stand alone backend.
Vacuum and reindex seem to be quite easy, as I can setup a small script with both commands. But what about pg_dump. That seems "somewhat" more complex.
Explain what exactly you are trying to do, why do you have to stop the postmaster ? If you request is due only to forbid the access then you can replace the pg_hba.conf with a "void" one and replace it again at the end of operations.
BTW vacuum, pg_dump, reindex are operations that can be performed with the server up and running.
on my database server I have a lot of scripts running. Some of them are
not under my control and some of these don't really behave nice. So when
my "maintainan ce period" starts they will still be running for hours and
take exclusive locks on 100,000+ rows in various tables. Which means
neither "vacuum full" nor "reindex" can get locks on these tables. Which
stalls these calls and delays them to a time when my well behaving but
heavy duty scripts start running again. Now these scripts will be
delayed be "vacuum" or "reindex" and when I get to my desk the next
morning the whole system is in overload.
I need to to disconnect any other users and do "vacuum full verbose
analyze" "reindex database" and reindex all tables. And for these I will
stop the postmaster and run a stand alone backend.
I figured that doing a nightly backup would be a good idea and running
it in stand alone mode will speed up the process drastically.
Ulrich
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
Ulrich Wisser wrote: Explain what exactly you are trying to do, why do you have to stop the postmaster ? If you request is due only to forbid the access then you can replace the pg_hba.conf with a "void" one and replace it again at the end of operations.
[snip] I need to to disconnect any other users and do "vacuum full verbose analyze" "reindex database" and reindex all tables. And for these I will stop the postmaster and run a stand alone backend.
I figured that doing a nightly backup would be a good idea and running it in stand alone mode will speed up the process drastically.
Don't think it makes much difference, assuming you're the only one
connected. I'd follow Gaetano's idea and have separate
postgresql.conf/pg_hba.conf files.
1. stop PG
2. swap conf files
3. start PG
4. maintenance
5. stop PG
6. swap conf files back
7. start PG
A separate postgresql.conf lets you have different sort_mem values etc.
for your nightly maintenance too.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Hi,
Citing Ulrich Wisser <ul***********@ relevanttraffic .se>: on my database server I have a lot of scripts running. Some of them are not under my control and some of these don't really behave nice. So when my "maintainan ce period" starts they will still be running for hours and take exclusive locks on 100,000+ rows in various tables. Which means neither "vacuum full" nor "reindex" can get locks on these tables. Which stalls these calls and delays them to a time when my well behaving but heavy duty scripts start running again. Now these scripts will be delayed be "vacuum" or "reindex" and when I get to my desk the next morning the whole system is in overload.
I need to to disconnect any other users and do "vacuum full verbose analyze" "reindex database" and reindex all tables. And for these I will stop the postmaster and run a stand alone backend.
How about stopping the postmaster, running vacuum and reindex in the
standalone backend, then starting the postmaster with a different
pg_hba.conf and optionally setting a few options from postgresql.conf
from the commandline (e.g. MAX_CONNECTIONS ,
SUPERUSER_RESER VED_CONNECTIONS ) to limit access to the server to the
user doing the dump and then doing the dump. When it is finished, you
could restart the postmaster with the proper runtime environment in
place. Would this work?
I figured that doing a nightly backup would be a good idea and running it in stand alone mode will speed up the process drastically.
It seems, we can't try if it is really drastically faster than just
starting the postmaster with restricted access and then doing the dump.
Another option would be to look at the source to determine what pg_dump
actually does and write a sql-script to come as close as possible to that
and postprocess the output of feeding this to the standalone backend to
produce again sql which could be loaded by psql.
Probably a lot of work, but perhaps doable if you only care about the
actual data and not about triggers, functions, access permissions etc. etc.
Regards,
Daniel
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
Ulrich Wisser <ul***********@ relevanttraffic .se> writes: I need to to disconnect any other users and do "vacuum full verbose analyze" "reindex database" and reindex all tables. And for these I will stop the postmaster and run a stand alone backend.
I think the real problem here is stone-age maintenance procedures ;-)
You shouldn't need to do vacuum full on a regular basis, and you
shouldn't need to do reindexing on a regular basis either. Update
to 7.4, if you aren't using it already, and replace these procedures
by plain vacuums run often enough to keep the DB from bloating (a look
at your FSM parameters would be advisable too).
I figured that doing a nightly backup would be a good idea and running it in stand alone mode will speed up the process drastically.
No it won't.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: none |
last post by:
Howdy all,
I'm wondering if someone could give some direction on a problem I have
or share their experiences.
I'm wanting to create a little PHP application that will run on a
local machine and use ODBC to connect to a Access database on that
machine. A series of forms will interact with the database but if I
understand correctly, I need some way to parse the PHP code. Is there
a stand alone parser that could be used in place of a...
|
by: Takeshi |
last post by:
Hi All,
I'm new to dotNET. I am (thinking of) building the presentation layer of
my application in dotNET. Communication with the backend will be done by
subject based (multicast) messaging - running on a Unix backend, with a
windows client (C Win32 DLL) resident on the front end.
I want to have a single code base for both a stand alone version of the
application (rich user interface, faster responses via local cacheing
etc), as well...
|
by: David Pendrey |
last post by:
I was wondering if it is at all posible to write a stand alone .EXE program
in Visual Studio .NET. Hopefully in VB.NET but if not another language would
be ok. Thanks for the assistance
|
by: Howard Lowndes |
last post by:
My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2
I have no difficulty inserting and managing BLOBs into the Large Object
system table, and I have a user table called images which maintains the
relationship between the BLOB loid and the identity that relates to it in
my user tables. So far so good.
When I RTFM obout psql it refers to the \lo_import, \lo_list, \lo_export
and \lo_unlink functions.
|
by: Ed L. |
last post by:
We are seeing what looks like pgsql data file corruption across multiple
clusters on a RAID5 partition on a single redhat linux 2.4 server running
7.3.4. System has ~20 clusters installed with a mix of 7.2.3, 7.3.2, and
7.3.4 (mostly 7.3.4), 10gb ram, 76gb on a RAID5, dual cpus, and very busy
with hundreds and sometimes > 1000 simultaneous connections. After ~250
days of continuous, flawless uptime operations, we recently began...
| |
by: RC |
last post by:
I am starting a project to track inventory for a medium size business.
The business would like to start off running the Access database on a
single laptop and move the laptop from one area of the warehouse to
another area of the warehouse as the database is needed. They want to
start this way to get the inventory tracking system in use as quickly
as possible. After the inventory tracking system gets refined and
updated so the process is...
|
by: jack turer |
last post by:
I have a database in pgsql (7.3.2) on redhat 9.
When I try a 'pg_dump mydb' to back up the database, I get:
pg_dump: could not find namespace with OID 2200
Verbose version is:
-bash-2.05b$ pg_dump -v mydb | more
pg_dump: saving database definition
pg_dump: reading namespaces
pg_dump: reading user-defined types
|
by: Alexander Cohen |
last post by:
(sorry for the double post if there is one - i sent the mail to the
lisyt from the wrong address)
Hi,
Im passing this in the commmand line to start up the PostgreSQL server:
../pg_ctl start -w -D /Volumes/GROUCH\ 2/Database3
but its always giving me this error:
|
by: jim-on-linux |
last post by:
py help,
The file below will run as a stand alone file.
It works fine as it is.
But, when I call it from another module it locks
my computer, The off switch is the only
salvation.
This module when run as a stand alone, it will
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |