On Tue, Mar 30, 2010 at 10:07:54PM -0400, Dai, Tino wrote:
I'm not a dba. I'm a sysadmin by training. Is there some way to
mirror the disks at the OS level? And then move it to the new
machine. Just a though, I don't know the exact steps. But if you
are interested, I
Hi Renato,
dump/restore is the way to go. I suppose, you don't want to compile
Postgres for 32 bit on the new machine which might work and might allow
you to do the PITR migration.
And downtime is not an option is always a sign of insufficient
planning beforehand. There is no system which
Hi Marc,
On Fri, Oct 23, 2009 at 02:48:58PM +0200, Marc Mamin wrote:
I'm using pg_dump intensively, and until now using the plaintext format
which allows me to pipe the output to pigz (http://zlib.net/pigz/)
This is the fastest way I've found to generate compressed backups.
(These backups
Hi Marc,
On Fri, Oct 23, 2009 at 03:52:16PM +0200, Marc Mamin wrote:
You might add pigz as a post-processing step and disabling compression in
pg_dump.
The problem with this solution is that it make it necessary to
decompress the dump entirely before using pg_restore (or did I miss a
Hi Dick,
On Wed, Aug 19, 2009 at 09:23:22PM +0200, Dick Visser wrote:
A bit of a long first story, hope someone is able to help...
We have got a Ubuntu 8.04 server running Postfix, which reads its
mailing list subscription files, aliases, virtual, domains, etc from a
Postgres database.
On Fri, Jul 10, 2009 at 08:49:40AM -0500, Kevin Grittner wrote:
So, what's a good value to set to effective_cache_size with 10 Gb
RAM?
It depends on how many other things you have on the system which are
using RAM, but probably something around 9GB would be appropriate.
Maybe even
Hi Rafael,
On Thu, Jul 09, 2009 at 07:18:55PM -0300, Rafael Domiciano wrote:
Today, I've upgraded a dedicated postgres server, from 2 Gb to 10 Gb.
Everything gone well.
But, I would like shared buffers to use at least 5 Gb of the total memory.
Setting kernel.shmmax with 6291456000 (6000
On Fri, Jun 19, 2009 at 05:50:25PM +0800, Tony Liao wrote:
Hi,All
for example,I have a query as this
select id,product_id from table_name where product_id in
(6,3,4,10,7) order by .
the results I want to get as bellow:
Hi Igor,
On Mon, Jun 15, 2009 at 01:15:30PM -0700, Igor Polishchuk wrote:
2. We can kill the idle sessions periodically. This will free up a big chunk
of memory already allocated to the sessions. The application will gradually
reestablish the connections, and the new sessions will start with
Hi Roland,
On Mon, Jun 08, 2009 at 12:23:06PM -0500, Roland Hughes wrote:
I have had a question for some time and cannot seem to find an answer.
Is there a way to add pre-existing tablespace to a fresh Postgres install?
Typically I create tablespace on some TB drives and place all
am Tino Schwarze wrote:
Hi Roland,
On Mon, Jun 08, 2009 at 12:23:06PM -0500, Roland Hughes wrote:
I have had a question for some time and cannot seem to find an answer.
Is there a way to add pre-existing tablespace to a fresh Postgres
install?
Typically I create tablespace
On Tue, Jun 09, 2009 at 09:10:20AM -0500, Roland Hughes wrote:
On Tuesday 09 June 2009 08:55:18 am Tino Schwarze wrote:
On Tue, Jun 09, 2009 at 07:57:34AM -0500, Roland Hughes wrote:
This has not been the case with 64-bit OpenSuSE. A fresh re-install of
the OS required I recreate
Hi Roland,
On Tue, Jun 09, 2009 at 09:31:43AM -0500, Roland Hughes wrote:
While it shouldn't be much data, it is also non-robust. With robust
databases, you can create as many databases as you want in as many places
as you want. A fresh install means that you only have to tell the
Hi pg043g9j2g (or what's your name?),
For various reasons I had to abandon that OS install and
reformat/reinstall WinXP. Time passes... Today I am looking to recover
my work and basically resume where I left off.
One problem is that I never did an export of the old database using
Hi Tom,
On Wed, Apr 01, 2009 at 08:58:54PM -0400, Tom Lane wrote:
Maybe we should remove the code and make
VACUUM FULL do the table-rewrite thing.
What do you mean with the table-rewrite thing, exactly?
Like CLUSTER, except not bothering to sort the rows: just seqscan the
table,
Hi Rafael,
On Wed, Apr 01, 2009 at 06:51:00PM -0300, Rafael Domiciano wrote:
I have some doubts about Vacuum Full. There We go:
1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
dead space on the disc, and reorganize the relation at the physical
level?
If it's
On Wed, Apr 01, 2009 at 08:09:31PM -0400, Tom Lane wrote:
Nowadays, with many many new users, and no historical context, they do
just take it for a better kind of vacuum when in fact it is really
like reindex to indexes. I'd vote for rebuild [table]; as the new way
to spell vacuum full;
On Tue, Mar 31, 2009 at 08:31:43AM -0300, Agustin Ignacio Genoves wrote:
I guess you should use Bytea
Or go for large objects.
Greetings:
I need to use a PostgreSQL Data Base to store different kinds of files, eg:
images, videos. So, I'm wondering what data type should I use.
Hi there,
I'm going to pg_restore a database dump of about 220 GiB (uncompressed,
but most data is BLOBs). The machine has 8 GiB of memory and 8 cores.
Is there any advice to speed up restoring, postgresql.conf-wise?
I already have a script which does the data loading and index creation
in
Hi Roger,
On Fri, Feb 13, 2009 at 01:56:32PM -0800, Roger Ging wrote:
!DOCTYPE html PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN
Please don't post HTML mails to mailing lists. Thanks.
I can only answer a couple of the questions at the moment.nbsp; I had to
kill the vacuum full and do a
Hi Mike,
I am newbie with PostgreSQL. I have separate table space for my
databases, which is located in separate folder, which is different to
default. I would like to carry out file system level backup. Do I need
to backup only folder with my table space or all pg folders.
Do you know
Hi,
I am getting this error when I try to access the database in another computer
within the same network.
could not connect to server: Connection timed out (0x274C/10060). Is the
server running on host XX.XX.XX.XX and accepting TCP/IP connections on port
5432?
[...]
My
not be enough).
HTH,
Tino.
--- On Fri, 5/12/08, Tino Schwarze [EMAIL PROTECTED] wrote:
From: Tino Schwarze [EMAIL PROTECTED]
Subject: Re: [ADMIN] Remote access
To: pgsql-admin@postgresql.org
Date: Friday, 5 December, 2008, 12:53 AM
Hi,
I am getting this error when I try to access
postgresql (reload might not be enough).
HTH,
Tino.
--- On Fri, 5/12/08, Tino Schwarze postgresql(at)tisc(dot)de wrote:
From: Tino Schwarze postgresql(at)tisc(dot)de
Subject: Re: [ADMIN] Remote access
To: pgsql-admin(at)postgresql(dot)org
Date: Friday, 5 December, 2008, 12:53 AM
On Wed, Nov 26, 2008 at 06:13:41PM -0700, Fabricio wrote:
I have an IBM server with NUMA architecture, two nodes with 4 CPU quad
core and 64 GB of RAM each, and PostgrSQL 8.3.5. Is there any way to
avoid the performance degradation when the load goes up and used the
two nodes? I understand
On Thu, Nov 27, 2008 at 09:13:04AM +0530, AmitKumar Jain wrote:
I am using PostgreSQL 8.3 version . The autovacuum process is stucking
my system as it automtically start even though its off is
postgresql.conf.
2. Is there any benchmark tool for Autovacuum process or can we know how
On Mon, Nov 10, 2008 at 09:50:09AM -0600, Dana Holland wrote:
Is it at all possible to get pgsql installed on a system without access
to a shell? A friend is wanting me to install a particular bulletin
board system on her website; however, her hosting plan only provides the
MySQL database
On Mon, Nov 10, 2008 at 10:54:57AM -0700, Scott Marlowe wrote:
Is it at all possible to get pgsql installed on a system without access to a
shell? A friend is wanting me to install a particular bulletin board system
on her website; however, her hosting plan only provides the MySQL database
Hi,
On Thu, Nov 06, 2008 at 04:15:02PM +0530, brahma tiwari wrote:
My database server db01 is on linux environment and size of base folder
increasing very fast unexpectedly(creating renamed files of 1 GB in base
folder like 1667234568.10)
This sounds like your max_fsm_pages setting is too
On Mon, Sep 22, 2008 at 09:43:29AM -0400, Brian A. Seklecki wrote:
BTW: What version of PG are you using?
We need to kill apache P-conn pools from a an application/web server
cluster for a specific database. We use a load-balancer, so we shut
down the HA L4 Service VIP that the web servers
On Fri, Aug 15, 2008 at 11:09:02AM +1000, [EMAIL PROTECTED] wrote:
Nice, that has cleared it up.
I am on 8.1 also.
On my test box, a standard dump took 6m 26sec a -Fc dump took 11min 2sec.
That's not a great difference, but the size difference is quite noticeable.
You might use
On Fri, Jul 25, 2008 at 04:49:48PM +0530, neo3 matrix wrote:
I have a table property_details having columns (mid,pid,pv,vt)
Accidentally , i fired a query
delete from property_details where mid=27617 and pid=20 or pid=21 or pid=22
, which resulted in deletion of 418 rows which had pids 21 and
On Sat, Jul 19, 2008 at 04:29:00PM +0530, Kartik wrote:
hello there,i am new to postgresql
i am using postgresql 8.3.3 and i am writing one whole transaction. i want
to know how to set auto commit off
because if any error occours i want the whole transaction to be rolled back.
when i tried
On Fri, Jul 18, 2008 at 04:29:42PM +0700, ria ulfa wrote:
i want to copy table from file with command:
COPY log FROM '/tmp/logfile.txt';
but there is error:
ERROR: could not open file /tmp/logfile.txt for reading: Permission
denied
what can i do for repair this error??
thanks for
On Wed, Jul 09, 2008 at 06:23:10PM +0900, Eko Prasetiyo wrote:
Now i have pgsql 8.0 server installed on CentOS Linux (Redhat).
How to install pqsql 8.2 on different directory and port ?
In Windows we can easily do it.
Just download the source and use for example
./configure
On Mon, Jul 07, 2008 at 02:19:19PM -0400, Carol Walter wrote:
I asked this question a while ago and I didn't get an answer.
Perhaps I asked the question in a manner that wasn't clear. I
upgraded from 8.1.3 to 8.2.3. I left the 8.1.3 database intact in
case I had to revert to it.
On Fri, Jul 04, 2008 at 06:50:43AM -0700, Lennin Caro wrote:
try this
select pg_cancel_backend(processid_db);
if the command not work kill the process in os
kill -9 proccessid_os
*NO*! Never kill -9 a postgres backend!!! This is like a headshot, no
chance to clean up. You will make your
On Thu, Jul 03, 2008 at 09:10:47PM +0200, Dimitri Fontaine wrote:
I've considered creating a tablespace in a directory owned by the
user , so I can use Linux quotas to prevent higher disk usage , but
this turned out be a bad thought, as all the files are anyway owned
by the postgres
On Tue, Jul 01, 2008 at 03:12:39PM +0700, ria ulfa wrote:
i use linux fedora 7 and database PostgreSQL 8.
i want to copy table in postgre to a file.
i used code : simps=# COPY log TO '/root/documents/log.sql';
note= simps: name of database
but there is error: could not open file
On Mon, Jun 30, 2008 at 02:30:23PM +0200, Thomas Bräutigam wrote:
I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra.
Thats plenty of data.
What do you guys recommend on RAID Levels for this Database. Which
does Postgres recommend, and with which do Postgres run very good or
in the
On Mon, Jun 30, 2008 at 02:52:05PM -0300, Rafael Domiciano wrote:
Folks, I am configuring a new Postgres Server, that's gonna substitute the
critical server of the enterprise.
I have a good machine:
Quad-Core 2.5 Ghz
4 Gb RAM
1 Dedicated HD 300 Gb Sata for the PostgreSQL Directory
If it's
On Wed, Jun 04, 2008 at 11:43:24AM +0200, Rudolf van der Leeden wrote:
I'm currently testing a 25G Postgres 8.3.0 database on Apple Xserve/
Intel with Mac OSX 10.5 (Leopard).
There are two identical machines with identical configurations (hw
and sw).
Running a pg_restore of a binary
On Mon, Jun 02, 2008 at 03:16:54PM -0700, [EMAIL PROTECTED] wrote:
I'd like to use postgres to store my secret data in a way that only
me (the DBA, owner of the table) can access the talbe while the root
(system administrator) who installed and maintains the server - will
not be able to see
On Tue, May 13, 2008 at 01:18:24PM -0600, kevin kempter wrote:
we seem to be getting deadlock quite a lot. We have a python process
that does the following and we keep getting deadlocks. Is this a real
deadlock or maybe a wait condition? Any help in debugging / resolving
this would be
Hi James,
On Mon, May 12, 2008 at 09:25:34AM +0200, James Farrugia wrote:
First of all thanks for the immediate replies!
Was actually waiting for the right moment to upgrade to 8.3 but migrating a
live 1Tb database is a bit daunting especially if you have never done it
before (as in my
On Sun, May 11, 2008 at 09:29:01AM -0500, Potluri Srikanth wrote:
can anyone provide me the security ACCESS matrix in prostgresql ?
Have a look at the fine manual:
http://www.postgresql.org/docs/8.3/static/privileges.html
HTH,
Tino.
--
What we resist, persists. (Zen saying)
On Wed, Apr 16, 2008 at 12:58:29PM +0300, Achilleas Mantzios wrote:
Hi, i just started the process of migrating from 7.4 to 8.3.1.
On restoring, apart from the easily explainable ERRORs (both in
DDL, COPY), i got some ugly ERRORs denoting foreign key constraint
violations. Since the
On Wed, Apr 16, 2008 at 12:35:26PM +0300, Achilleas Mantzios wrote:
Hi, i just started the process of migrating from 7.4 to 8.3.1.
On restoring, apart from the easily explainable ERRORs (both in DDL, COPY), i
got some ugly ERRORs
denoting foreign key constraint violations.
Since the dump is
On Tue, Apr 08, 2008 at 11:42:34AM +0200, Johann Spies wrote:
12501 ?S 0:00 /usr/lib/postgresql/8.1/bin/postmaster -D
/var/lib/postgresql/8.1/main -c
config_file=/etc/postgresql/8.1/main/postgresql.conf
12504 ?D 0:54 \_ postgres: writer process
12505 ?S
On Tue, Apr 08, 2008 at 12:13:36PM +0200, Johann Spies wrote:
This took about a week on a 2xCPU quadcore server with 8Gb RAM.
This is not the most interesting thing here. What disk I/O subsystem do
you use? At least a hardware RAID controller with RAID 0 or 10 should
be used, with
On Tue, Apr 01, 2008 at 04:44:29PM +0200, Shavonne Marietta Wijesinghe wrote:
I have my postgresql(ver. 8 ) db on one computer (as localhost) and i'm
trying to access it from another computer but i get the error
could not connect to Server: Connection refused
I followed the guide and
On Tue, Apr 01, 2008 at 05:52:01PM +0200, Shavonne Marietta Wijesinghe wrote:
Thanks Tino. But what do you mean by netstat -tln i use the pgadmin.
So you're on Windows? You didn't mention any operating system...
What does Postgres say on startup?
Tino.
--
„Es gibt keinen Weg zum Frieden. Der
.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
www.forteego.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
on command line, I would set the
appropiate options in postgresql.conf - max_connections and
shared_buffers.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
www.forteego.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
---(end of broadcast
store anything UTF8. The performance penalty should be very tiny.
I presume this (admin) is the proper PostgreSQL list to post this to.
Yes, it is.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
be quickstart chapters or HOWTOs.
Bye,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
in optimizing queries for no-cache scenario if in
practice, the cache will have the data. I know of real-live applications
where the first query takes some time and all subsequent are fast.
That's what caches are for.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino
offset 10;
will run the query and show you how the query plan looks and how much
time is spent. It is likely to say SeqScan somewhere - a sequential
scan of the tabe.
HTH!
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
is going on and
whether it's encrypted.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
adjust any settings in postgresql.conf (probably based
on the 8.1 ones)? Do you see anything in PostgreSQL's log file? What
kind of machine are you using (there are a lot of different Sun
boxes...)
Bye,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze
! But
it's good for testing.
Bye,
Tino.
--
www.craniosacralzentrum.de
www.lebensraum11.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project
. netmask /8 says: first 8 bits have to match,
that is, first number must be equal. /16 says: first 16 bits have to
match - first and second number must be equal. /32 means: IP has to
match.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.lebensraum11.de
www.spiritualdesign-chemnitz.de
Tino Schwarze
if that's what he
wants to accomplish.
If you're trying to protect against something else, please clarify.
+1
Bye,
Tino.
--
www.craniosacralzentrum.de
www.lebensraum11.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end
installing knoda,
mergeant, rekall and look at these (I don't know them, just looked into
YaST for available database stuff).
I prefer using psql directly...
Bye,
Tino.
--
www.craniosacralzentrum.de
www.lebensraum11.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Parkstraße 17h * 09120
.x version, follow the instructions
here: http://www.postgresql.org/docs/8.2/interactive/installation.html
(You need to install the packages readline-devel and zlib-devel as well
as the gcc compiler).
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze
, then calculates lock holding time? Hm, or I'll
just watch for not yet granted locks and set a threshold if there are
more than x transactions waiting.
Any further hints or ideas?
Thanks,
Tino.
--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
.
--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)---
TIP 6: explain analyze is your friend
();
try
{
// perform work
success = true;
}
finally
{
connection.closeTransaction (success);
}
HTH,
Tino.
--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)---
TIP 7
,l.pid,l.granted,l.mode from pg_class
c,pg_locks l where l.relation = c.oid order by l.pid, l.mode;
HTH,
Tino.
--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)---
TIP 9
shell script to determine the size of all
databases:
#!/bin/bash
#
# Shell script to determine disk usage of PostgreSQL databases.
# by Tino Schwarze/Community4you
PGDATADIR=/data/pgsql/74
PGPORT=5432
PSQLBIN=/opt/pgsql-7.4.3/bin/psql
PGOPTS=-U postgres
echo PostgreSQL 7.4.3 database sizes
72 matches
Mail list logo
