I needed both UTF8 and Latin-1. I accomplished this by initdb with the LOCALE
set to C. That lets me create dbs "with template0 encoding='Latin-1'" as well
as "encoding=UTF8," FWIW...
Original message
From: Marc Fromm
Date: 10/10/2013 5:39 PM (GMT-06:00)
To: pgsql-admin@po
Never RAID 5 for a database. When I say "never," I can give you edge-case
scenarios, but you're basically taking a 4x overhead on all writes. Now, RAID-0
is a bad choice as well, since JBOD has no replication, but it sounds like you
might have that end under control.
Original message -
Server is set to "Chicago." CDT (or CST...are we in or out? I never
remember...) GMT -6/5.
Original message
From: Tom Lane
Date: 08/13/2013 7:54 PM (GMT-06:00)
To: Scott Whitney ,Scott Whitney
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] WTF? 9.2.4 Log
Everything you need to see is right here:
[root@ pg_log]# ls -lrt
total 5924
-rw--- 1 postgres postgres 708669 Aug 8 18:59 postgresql-Thu.log
-rw--- 1 postgres postgres 669505 Aug 9 18:59 postgresql-Fri.log
-rw--- 1 postgres postgres 03 Aug 10 18:56 postgresql-Sat.log
-rw-
I tested the hybrid approach during my months-long testing and performance
stuff, and I was a bit underwhelmed.
That said, what _I_ personally really needed was increase in peak iops. Using
spindles for "static" data (OS, some logs, and such) worked fine, but no matter
how I split up the pg st
When you say "16 10K drives," do you mean:
a) RAID 0 with 16 drives?
b) RAID 1 with 8+8 drives?
c) RAID 5 with 12 drives?
d) RAID 1 with 7+7 drives and 2 hotspares?
We moved from a 14 FC drive (15k RPM) array (6+6 with 2 hotspares) to a 6 SSD
array (2+2 with 2 hotspares) because our iops wo
up and running on Saturday, I was wondering whether full vacuum could have been
to blame for the servers getting out ot sync.
Thanks,
Scott Whitney
I've got replication setup (streaming replication) between two 9.2.2 clusters.
Everything went just fine on that, and it's working as expected.
Now, obviously, I'll know if my primary goes down. I just monitor him as usual.
How do I tell whether the standby has gone out of sync for whatever re
I'd hate to contradict Tom, and since ^ is probably a typo, and you likely
meant *, yes, that's likely, but be sure you understand the implications of
listening on all interfaces first. Just saying from an IT point of view.Tom
Lane wrote:Terry Khatri writes:
> I am having problems making any c
ook accurate?
Anyone have recommendations for companies you would use to pay for additional
vetting?
Thanks in advance.
Scott Whitney
PS: I have written a multi-proc script (in Python, Linux specific at the
moment) for pg_dump that you can use to pg_dump and restore said dumps. If
anyone'
you can pick the tables you really want get
> replicated there.
> For the backup turn to hot backup (tar $PGDATA)+ archiving, easier,
> faster and more efficient rather than a logical copy with pgdump.
> A.A
> On 04/25/2012 09:11 AM, Scott Whitney wrote:
> > Hello, everyone. I w
Hello, everyone. I want to throw a scenario out there to see what y'all think.
Soon, my cluster backups will be increasing in size inordinately. They're going
to immediately go to 3x as large as they currently are with the potential to be
about 20x within a year or so.
My current setup uses a
I'll be moving to PG9 (hopefully soon...probably 6 weeks).
At that time, I'll be setting up hot-standby with streaming replication to 2
sites. Off-siting my pgdumps nightly is no longer going to be possible in the
very near future, due to the size of the dumps.
So...what I had planned to do wa
What version, Matt? I had that problem back in 8.1x
- Original Message -
> I have a database that is utf8 and displays utf8 values correctly in
> psql. When dumped, it displays the utf8 characters incorrectly. ie.
> ö turns into Ã
> In the header of the dump file, I have:
> SET client_e
I dealt with this issue for years. It's not a pgadmin issue. In my case I had ipchains messing with me then iptables. Eventually Cisco. Each time I've moved to a new firewall, I have to re-solve this issue. Unless others are interested, take this offline and email me. Original message -
> > This is also interesting. I just allowed connections to template0
> > for the express purpose of vacuuming it, did a full vac on
> > template0, and that did NOT clear up the hanging clogs.
>
> Was it a VACUUM FREEZE followed by a CHECKPOINT? Did you also cover
> that new database?
Yes, yes an
> > > > > If you have hardware problems like that you have way more
> > > > > problems. You could have corruption (silent) occurring in any
> > > > > of the other
> > > > > database files. Good luck.
> > > >
> > > > I am, in fact, aware of that, but every single machine ever
> > > > manufactured wi
> On Thu, Mar 10, 2011 at 07:56:26AM -0600, Scott Whitney wrote:
> > >
> > > If you have hardware problems like that you have way more
> > > problems. You could have corruption (silent) occurring in any of
> > > the other
> > > database files. Good
> > Ooops...I accidentally took this off list, as Kevin was nice enough
> > to point out.
> >
> >
> > >> What am I looking for?
> >
> > >Outliers.
> >
> > > Yeah. It's just those 2. I'd assume that the db I created
> > > yesterday would be an outlier, but template0 has been there all
> > > along (o
Ooops...I accidentally took this off list, as Kevin was nice enough to point
out.
>> What am I looking for?
>Outliers.
> Yeah. It's just those 2. I'd assume that the db I created
> yesterday would be an outlier, but template0 has been there all along
> (of course) and is still listed as 648,
I had this issue back in pg 7.x, and it was resolved by using -a in vacuumdb.
I'm having it again in v8.4.4.
So, my pg_clog directory contains files going back to Jul 13 of 2010.
Every Saturday, I run:
"vacuumdb -a -v -F"
I _thought_ that was supposed to clear those out. Am I wrong?
which, I think, is when the server was last
restarted.
I'll try the -F on Sat and see if that resolves the issue. I'll also have logs
available at that time, assuming all goes well.
Thanks for the advice.
Scott Whitney wrote:
> Each Saturday, I run: vacuumdb -a -v. I hav
Hello. I posted this once before, but I'm encountering it again.
Each Saturday, I run: vacuumdb -a -v. I have autovac on all the time.
However, my pg_clog directory lists clog files going back to July.
This is pg 8.4.4 on Linux (CentOS 5.5).
I know this isn't a whole lot of information at t
That only works in the event that you have PG listening on port 5432.
A product like Spiceworks will provide much more detail, presuming you have the
IT credentials to talk to the machines.
nmap is the way to go. Try to scan for port 5432 in a range of IP of your
LAN.
~Fred
Linkedin pro
There are ways to enable it, but in general, yes, you're right. In AD you have
to do some magic to bind if you're not part of the actual AD forest.
Hi,
I'm not an AD expert myself, but I've read somewhere, that AD disallows
simple binds without using ssl.
You could try enabling ssl on AD,
There are a couple of ways. Assuming that it's the same database, and it's up
and running, you could do this:
Assuming:
table foo (col1 text, col2 int);
table bar (col2 text, col3 int);
insert into bar (select * from foo);
would stick everything from foo.col1 and foo.col2 into bar.col2 and
While I agree with JD, we ended up using a fiber solution through a fiber
switch with multi-path drivers (IBM DS4300). It did end up costing a few
thousand dollars with all of the drives, but the performance made it worth it.
The big thing you want to remember to consider with any storage optio
Well, there are quite a few more variables to consider, including:
a) speed of the drives
b) battery backed caching controller or not
c) throughput of the controller channels
d) your particular use
Point d is kind of the most important. We've got about 250 customers talking to
the same back
In practice, I find that theory holds up only erratically.
- "Greg Smith" wrote:
> Scott Whitney wrote:
> > On the 10k vs 15k rpm disks, there's a _lot_ to be said about that. I don't
> > want to start a flame war here,
> > but 15k versus 10k rpm ha
During the testing that I did when moving from pg7 to pg8 a few years back, I
didn't notice any particular performance
increase on a similarly-configured server.
That is, we've got 14 disks (15k rpm) striped in a single RAID10 array. Moving
the logs to an internal RAID
versus leaving them on
Will PG start at all? Manually, even? I'd say "go back to clean," first of all
by removing your from-source install, and reinstall the apt-get packages.
At that point, can you start postgres at all? If not, what do you system logs
and postgres startup logs say?
- "Jordz" wrote:
> I have
I've got about 44GB of data in a few hundred production databases. I'm using PG
8.1.4, but upgrading today (even to the latest 8.1) is not an option. I know, I
know. I wish it were, and it's slated here for q2, but I cannot even apply
maintenance patches without a full testing cycle.
My auto
I've got about 44GB of data in a few hundred production databases. I'm using PG
8.1.4, but upgrading today (even to the latest 8.1) is not an option. I know, I
know. I wish it were, and it's slated here for q2, but I cannot even apply
maintenance patches without a full testing cycle.
My auto-v
I've got about 44GB of data in a few hundred production databases. I'm using PG
8.1.4, but upgrading today (even to the latest 8.1) is not an option. I know, I
know. I wish it were, and it's slated here for q2, but I cannot even apply
maintenance patches without a full testing cycle.
My aut
--
From: pgsql-admin-ow...@postgresql.org
[mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Scott Whitney
Sent: Thursday, October 08, 2009 4:11 PM
To: 'Louis Lam'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How to tell what OS PostgreSQL is installed on.
Visual C++ is Microsoft&#x
Another option might be to look in the
select pg_show_all_settings() for the ident_file.
If the ident file is a DOS path (aka c:\somewhere\some dir\some file), you
know you're on a Windows-based system. If it's a UNIX path, select version()
will tell you specifically.
-Original Message-
Visual C++ is Microsoft's compiler for C++. If something is compiled with
Visual C++, it was COMPILED on Windows. There is a 99.9% chance that the
underlying machine is also Windows (unless you're talking about a VM, but,
still, the OS on which PG itself is running is, in fact, Windows).
To the be
e whether you're paging or waiting on storage. I
suspect you'll find that throwing hardware (memory, as a guess) at this
problem will solve it.
-Original Message-
From: S Arvind [mailto:arvindw...@gmail.com]
Sent: Monday, September 21, 2009 2:19 PM
To: Scott Whitney; pgsql-ad
There's an awful lot of information left out that would be very useful to
help advise you.
Restarting the postgres services on a daily basis is certainly nothing
that's going to corrupt your data or hurt your system, PROVIDED that it is
done correctly (ie: not killing the backend postmaster when s
>That's most likely because you have too small an FSM. Have you tuned
>that?
My settings are:
max_fsm_pages = 150 # min max_fsm_relations*16, 6 bytes
each
max_fsm_relations = 7 # min 100, ~70 bytes each
It's quite possible that those settings are too low, b
>> I'd like to phone in with a slightly different opinion on VACUUM FULL.
Yeah,
>> it should be avoided when possible, but it's not always possible. In our
>> case, I've got 300ish databases backing to a single database server. Each
of
>> those dbs has a couple of hundred tables and a hundred or mo
I'd like to phone in with a slightly different opinion on VACUUM FULL. Yeah,
it should be avoided when possible, but it's not always possible. In our
case, I've got 300ish databases backing to a single database server. Each of
those dbs has a couple of hundred tables and a hundred or more views. Th
Well, there are a lot of unanswered questions in your post.
What VM technology are you using? Does it support clustering or some such?
Do you need to backup the entire VM, or just the PG data?
Generally speaking, if the "machine" itself (installed OS and programs, etc)
are not changing, what I wo
So, I got pretty close to my max_connections setting, and I had to up it
last night. I did so, and I issued a pg_ctl reload. Everything _seems_
happy.
However, the script I use to monitor this has the number hard-coded in it
(well, it looks it up from the conf file, anyway), and I'd rather get the
wanted. :)
-Original Message-
From: Joshua D. Drake [mailto:j...@commandprompt.com]
Sent: Wednesday, January 28, 2009 12:47 PM
To: Scott Whitney
Cc: 'Hoover, Jeffrey'; 'Kevin Grittner'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Strange query problem...
On Wed, 2009
day, January 28, 2009 12:31 PM
To: Scott Whitney; Kevin Grittner; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Strange query problem...
How do other databases handle this? I tried it in SQLite and I get
different behavior (see below). Can someone try it in Oracle? In MySQL?
In Sybase
So, you're sayin' I ain't crazy? :)
-Original Message-
From: Hoover, Jeffrey [mailto:jhoo...@jcvi.org]
Sent: Wednesday, January 28, 2009 12:18 PM
To: Kevin Grittner; Scott Whitney; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Strange query problem...
Wow! I would neve
Um. How is this possible? Am I doing something very, very stupid, here?
mydb=# select * from time_recs where id_time_rec not in (select id_time_rec
from punch_time_recs);
id_time_rec | id_user | record_date | id_code_task | id_code_pay_type |
id_project | time_amount | comment | commit_state | i
upgrade policy, though.
After all, when I _do_ get hit by one of those bugs, I _will_ be asked why
we weren't upgraded. *sigh*
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Tuesday, January 13, 2009 4:16 PM
To: Scott Whitney
Cc: pgsql-admin@postgres
We're running
auto-vacuum, as we have been for years. A full vacuum happens every Saturday
night.
Any clever ideas? Requests for more information?
Thanks in advance!
Scott Whitney
Journyx, Inc.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Sorry for the repetition on some of this stuff. I'm just now learning it.
So, I've got my WALs archiving just fine, and I've got my base backup.
What I'd like to do is keep the standby server in recovery mode.
Docs say:
"If we continuously feed the series of WAL files to another machine that has
EMAIL PROTECTED]
Sent: Tuesday, October 21, 2008 3:10 PM
To: Scott Whitney
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR question with base backup
Scott Whitney wrote:
> The problem is that I create databases pretty regularly. Let's say I
create
> 3 in a week. I'm not looking f
I'm in the process of testing PITR recovery, and I have an issue.
My "data" directory is 30GB. Not huge, but it certainly takes awhile to tar
up.
My understanding is:
a) pg_start_backup
b) tar up
c) pg_stop_backup
d) restore tar file
The problem is that I create databases pretty regularly. Let's
I ran into this issue awhile ago. Here's my long internal tech note to my
dev guys on what I did. A bit modified for more genericism:
I'm in the process of migrating our internal db server, and I decided to use
the helpdesk as
my test database. It backed up fine last night. Something went horribly
I'm migrating a pg server (v 8.1.4) this weekend, and I have 2 dbs left on
it. One cannot be shut down until tomorrow. The last remaining one, I can
migrate today.
However, the software that talks to it is somewhat "hinky" and has several
places that connect -- there is no overall consolidated con
1:21 PM
To: Scott Whitney
Cc: Simon Riggs; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server
While I am not an expert on WAL, but again I question the merits of such
sophisticated HA configuration. Of course there are use cases for such
configs, but I am only advocating best price p
andby2 (at my office)?
Assume no auto-failover.
-Original Message-
From: Montaseri [mailto:[EMAIL PROTECTED]
Sent: Jun 26, 2008 12:51 PM
To: Simon Riggs
Cc: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server
I am not so sure of this arrangement's m
I've got 3 different database servers (db01, db02 and db03).
I would like to have a WAL standby server that replays logs for all 3 in
case one goes down, so I can promote that particular server.
Can I do this by installing 3 separate postmasters on this machine?
Obviously, if 2 went down at the s
found this by using "select * from attachments limit 1000 offset 0"
then incremented until I found the bad rec. :)
d) insert the recs manually into the new db
Just an FYI for anyone else who runs into this and doesn't want to use a
known good backup.
-Original Message-
From:
OTECTED]
Sent: Jun 20, 2008 11:41 AM
To: Scott Whitney
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Missing chunks from my toast...
"Scott Whitney" <[EMAIL PROTECTED]> writes:
> There goes my breakfast...
> pg_dump: ERROR: missing chunk number 0 for toast value 110439
There goes my breakfast...
pg_dump: ERROR: missing chunk number 0 for toast value 110439697
pg_dump: SQL command to dump the contents of table "attachments" failed:
PQendcopy() failed.
pg_dump: Error message from server: ERROR: missing chunk number 0 for toast
value 110439697
pg_dump: The comman
This seems to work for me:
template1=# select now() at time zone 'UTC';
timezone
2008-06-05 19:08:50.590806
(1 row)
template1=# select now();
now
---
2008-06-05 14:08:57.278037-05
Cheers
I don't really understand the postings I've found on this issue. This has
been going on for quite some time, but now that I'm regularly vacuuming this
db, I was looking for any info on how to fix this problem...I don't even
know enough about it to know what to include in this message.
This is the
select datid from pg_stat_database where datname='km';
select database_size(that_id);
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Carol Walter
Sent: Nov 13, 2007 9:35 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Help with command syntax?
Greeti
uumdb -f -v -z %s'
where %s is each database in the system. I don't have the log at the moment
to prove it ran Saturday, but, yeah it does.
So...
I'm performing a full vacuum on each database in the system every Saturday.
My pg_clog files date back to August 8th.
What am I doing
eta.com/ha-postgresql.html
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]
Sent: Oct 10, 2007 11:39 AM
To: Scott Whitney
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Using rsync for base backups for PITR
Scott Whitney wrote:
> I'm reading conflicting information
I'm reading conflicting information on this. Is this a supported technique?
start_backup
rsync the initial base backup
stop_backup
Then, periodically,
start_backup
rsync again
stop_backup
It's my opinion that this wouuld significantly cut down the 30GB base backup
I'll need to take for each PITR
it ran Saturday, but, yeah it does.
So...
I'm performing a full vacuum on each database in the system every Saturday.
My pg_clog files date back to August 8th.
What am I doing wrong?
Thanks,
Scott Whitney
Journyx, Inc.
---(end of broadcast)-
68 matches
Mail list logo
