Re: [ADMIN] PITR Based replication ...
On Tue, 4 Apr 2006, Thomas F. O'Connell wrote: On Apr 4, 2006, at 4:05 PM, Marc G. Fournier wrote: I'll be curious to hear stories of people using it for replication. The way I interpret replication, there's an available database (even if read-only) on both ends. With PITR/on-line backups, the way I understand it, there's no way to provide availability to the recovery database because it's in a process of continuous recovery. It qualifies as high availability in terms of a failover solution, but the recovery database is not actually available until something triggers it to recover, at which point any writing done to it causes it to cease to be a replicant of the base database. We started a project on it here: http://pgpitrha.projects.postgresql.org/ So far we have a working version of it in CVS which we are using at travelpost.com. You're correct, the secondary system is only available after you complete the PITR recovery, but it works well for us currently. Right now we just make base backups 3 times daily and restore all the way from the base when we cutover. The first thing we'll be changing is that methodology (i.e. we'll be going to a continuous recovery methodology). Hopefully we'll get some interest from more folks soon and get some good ideas flowing. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] PITR Based replication ...
On Apr 4, 2006, at 4:05 PM, Marc G. Fournier wrote: I know ppl are using it to do replication, but has anyone documented what is involved in doing so? thanks ... I'll be curious to hear stories of people using it for replication. The way I interpret replication, there's an available database (even if read-only) on both ends. With PITR/on-line backups, the way I understand it, there's no way to provide availability to the recovery database because it's in a process of continuous recovery. It qualifies as high availability in terms of a failover solution, but the recovery database is not actually available until something triggers it to recover, at which point any writing done to it causes it to cease to be a replicant of the base database. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] PITR Based replication ...
I know ppl are using it to do replication, but has anyone documented what is involved in doing so? thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] pg_stat_activity showing non-existent processes
Perhaps I might be able to help you track this problem down, but I could use some help. The limited amount of discussion on this I Googled up didn't help. We have never seen these messages before; we are assuming that their appearance now (we have been running OK since last August) is related to two recent changes we made to our Windows 2003 Server (SvcPk 1), PostgreSQL 8.0.4 installation: (1) started capturing statistics with the following config parms; all other parms were installation defaults: debug_pretty_print "on" log_min_duration_statement "60" log_min_error_statement "debug1" log_statement "ddl" log_truncate_on_rotation"on" stats_block_level "on" stats_command_string"on" stats_reset_on_server_start "on" stats_row_level "on" stats_start_collector "on" (2) added additional application workload to this server Don't understand the 'target machine' message, either; in this case, we are running the application and the database server on the same box. 2006-04-04 03:12:05 FATAL: could not read from statistics collector pipe: No error 2006-04-04 03:12:06 FATAL: could not write to statistics collector pipe: No connection could be made because the target machine actively refused it. 2006-04-04 04:16:58 FATAL: could not read from statistics collector pipe: No error 2006-04-04 04:16:58 FATAL: could not write to statistics collector pipe: No connection could be made because the target machine actively refused it. 2006-04-04 05:47:26 FATAL: could not read from statistics collector pipe: No error 2006-04-04 05:47:27 LOG: statistics collector process (PID 1776) was terminated by signal 1 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Tuesday, April 04, 2006 1:29 AM To: Kevin Grittner Cc: Jerry Sievers; pgsql-admin@postgresql.org; Peter Brant Subject: Re: [ADMIN] pg_stat_activity showing non-existent processes "Kevin Grittner" <[EMAIL PROTECTED]> writes: > It is probably related to something we've been seeing in the PostgreSQL > logs on the Windows servers: > [2006-04-03 08:28:25.990 ] 2072 FATAL: could not read from statistics > collector pipe: No error > [2006-04-03 08:28:26.068 ] 2012 LOG: statistics collector process (PID > 3268) was terminated by signal 1 We've heard reports of instability in the stats collector on Windows before, though I'm not sure if this is exactly the symptom --- check the list archives. Nobody's been able to track it down yet. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] log connections to database
Ben K. wrote:
> 3. dropping a role
>
> If the user is not the owner of the object, is the following an intended
> behavior, or could it be some misconfiguration on my side? (In fact this
> seems to give me the list of objects the user has acl on.)
>
> create group ddd;
> grant all on atable to ddd;
> \dp atable
> public | atable | table | {postgres=arwdRxt/postgres,ddd=arwdRxt/postgres}
> drop group ddd;
> ERROR: role "ddd" cannot be dropped because some objects depend on it
> DETAIL: access to table atable
Yup, this is intended and new in 8.1. You need to REVOKE the privileges
before being able to drop the role.
In 8.2 (current development) there is a commands to drop the objects
owned by the user, which additionally revokes all the privileges; and a
command to "give" the objects to someone else. They didn't make the 8.1
deadline however.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] pg_stat_activity showing non-existent processes
"Lane Van Ingen" <[EMAIL PROTECTED]> writes: > Don't understand the 'target machine' message, either; in this case, we are > running the application and the database server on the same box. > 2006-04-04 03:12:05 FATAL: could not read from statistics collector pipe: > No error 2006-04-04 03:12:06 FATAL: could not write to statistics collector > pipe: No connection could be made because the target machine actively > refused it. I think that's Microsoftese for ECONNRESET, ie, the kernel bounced a packet for lack of any listening process to deliver it to. The real question is what's causing the collector to fail (the "could not read"). While it'd be easy to make it retry read attempts, the reason it considers that FATAL is that it really should never happen. I'd like to find out exactly what's happening before we try to fix it. As Kevin mentioned, adding some more debug printout would be helpful. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] log connections to database
Sorry if these are basic questions. It's postgres 8.1 on unix.
1. logging to database
I'd like to log connection authentication info into one of the databases,
(it's ok to be on the same server as production server), but the logging
configuration in postgresql.conf doesn't seem to cover this scenario.
2. checking for permissions
Also, is there a way to get the list of objects a user has any permission
on? If not, other than using aclcontains, is there some type I can cast
relacl to get text to grep from? aclcontains doesn't seem to allow
regex.
3. dropping a role
If the user is not the owner of the object, is the following an intended
behavior, or could it be some misconfiguration on my side? (In fact this
seems to give me the list of objects the user has acl on.)
create group ddd;
grant all on atable to ddd;
\dp atable
public | atable | table | {postgres=arwdRxt/postgres,ddd=arwdRxt/postgres}
drop group ddd;
ERROR: role "ddd" cannot be dropped because some objects depend on it
DETAIL:
access to table atable
I couldn't see this from 8.0.
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Permissions
Tom Lane wrote: Milen Dzhumerov <[EMAIL PROTECTED]> writes: I've been trying to figure a few things lately but I could not so I'm asking on the list. I need to programmatically (through JDBC): 1. Figure out if the currently logged in user can create a schema 2. Figure out if a role has USAGE privileges on a schema 3. Figure out if a role has CREATE privileges on a schema 4. Figure out if a user can create a table in a schema I've been reading the online docs but I could nowhere see any examples of finding out that information through the information_schema schema. I was advised not to poke around with the pg_* schemas since they can change frequently. Check out the has_foo_privilege family of functions: http://www.postgresql.org/docs/8.1/static/functions-info.html I can retrieve all the necessary permissions for tables from the information_schema.table_privileges table with no problems but I'm puzzled about database & schema privileges. I think that those privileges are PG extensions to the standard, which is why the standard information_schema views don't know about them ... regards, tom lane Thanks a lot. That's all I needed. Kind regards, gamehack ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
