Setting PGDATA

csn

What's the best way to set PGDATA? Put it in the user postgres's .bashrc, .profile, or in /etc/bashrc, /etc/profile, or other?

Sxooter

There are many opinions, I put it into the postgres users ~/.bash_profile or .bashrc myself.

chriskl

Put it in /etc/login.conf (FreeBSD, don't know about Linux)

Chris

csn

I put it in postgres's .bash_profile. Wasn't sure if it needed to be available to all users, but I guess postmaster/postgres (and other server apps typically run only by user postgres) are the only programs that use PGDATA (and not psql, and other client apps).

BTW, how would you run/access multiple data dirs - run separate postmasters on different ports?

Sxooter

BTW, how would you run/access multiple data dirs - run separate postmasters on different ports?

that's one way. If you want to have truly seperate server instances, that'll work, but at the cost of raising system resource usage, since it will require each database to have many files open, lots of shared_buffers, etc...

the other way is using initlocation and alternate locations.

alternate locations

I have used this before and it works quite well.

Sometime around 7.5 we'll likely see a table spaces patch come out, which will make the alternate locations stuff look like mud pies in the school yard... 🙂

csn

Originally posted by Sxooter
Sometime around 7.5 we'll likely see a table spaces patch come out, which will make the alternate locations stuff look like mud pies in the school yard... 🙂

Hmm, don't know if I'd ever use that - is there a URL with further info? Any idea what else is on 7.5's TODO?

Sxooter

7.4's still in beta, but it's looking like Win32 native operation, table spaces, and Point in Time Replication. If anything slips, it's likely to be PITR.

Point in Time Replication would be a HUGE win for postgresql. It would allow you to have a database that could basically be recovered to a known state in an exact moment in time after a hardware failure or system level crash.

It also lays the ground work for transaction log based replication, which should be a very fast synchronous sing master / multi slave system.

No guarantees of course, but that's the big stuff still in the works.

The TODO list is here:

http://developer.postgresql.org/todo.php

Note that lots gets put on the TODO list that isn't being worked on. It's kind of a grab bag for hackers to look at and ask if anyone else is working on it and get advice before trying to implement it.

In the mean time, ERserver is due out around 7.4's final release date as well. This is a replication server written in Java and well tested. I believe it's the replication that afilias usesd to host the .info and .org domains.

csn

Some TODO list - a lot of it is greek to me 😉.

I was reading about Oracle's next version - it was saying it'll be possible to query the database's data at specific points in time - i.e. you'd be able to query data as it was a week ago, a month ago, a year ago, etc. Sounds pretty wild to implement - would PG's PITR lend itself to something similiar?

Sxooter

What you are describing is generally called "time travel" in database circles. Postgresql used to support it natively, but it cause much trouble, and the code that did it was a bit crufty and it was removed long ago.

now that it runs on MVCC locking, it is quite possible to implement a system that could do time travel easily.

In fact, some folks have hacked around and made some user contrib modules (contrib/spi is one) that let you do this kind of thing in an "accounting trail" mode that works quite well.