[ADMIN] db crash, streaming rep slave will not start
Hi All; A client's master database crashed, they tried to startup the streaming replication slave and it refuses to start. See the log details below... thanks in advance for any help Master log: 2013-07-30 16:23:01 MDT PANIC: corrupted page pointers: lower = 0, upper = 0, special = 0 2013-07-30 16:23:02 MDT LOG: server process (PID 17539) was terminated by signal 6: Abort trap 2013-07-30 16:23:02 MDT LOG: terminating any other active server processes 2013-07-30 16:23:02 MDT [local]WARNING: terminating connection because of crash of another server process 2013-07-30 16:23:02 MDT [local]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2013-07-30 16:23:02 MDT [local]HINT: In a moment you should be able to reconnect to the database and repeat your command. 2013-07-30 16:23:02 MDT [local]WARNING: terminating connection because of crash of another server process 2013-07-30 16:23:02 MDT [local]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2013-07-30 16:23:02 MDT [local]HINT: In a moment you should be able to reconnect to the database and repeat your command. 2013-07-30 16:23:02 MDT [local]WARNING: terminating connection because of crash of another server process 2013-07-30 16:23:02 MDT [local]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2013-07-30 16:23:04 MDT [local]FATAL: the database system is in recovery mode 2013-07-30 16:23:04 MDT LOG: archiver process (PID 1826) exited with exit code 1 2013-07-30 16:23:04 MDT 192.168.131.2FATAL: the database system is in recovery mode 2013-07-30 16:23:04 MDT LOG: all server processes terminated; reinitializing 2013-07-30 16:23:04 MDT LOG: database system was interrupted; last known up at 2013-07-30 16:21:33 MDT 2013-07-30 16:23:04 MDT LOG: database system was not properly shut down; automatic recovery in progress 2013-07-30 16:23:04 MDT LOG: consistent recovery state reached at 1179D/8B7E7EF8 2013-07-30 16:23:04 MDT LOG: redo starts at 1179A/C1001EA8 2013-07-30 16:26:48 MDT LOG: record with zero length at 1179D/AC2591A8 2013-07-30 16:26:48 MDT LOG: redo done at 1179D/AC259168 2013-07-30 16:26:48 MDT LOG: last completed transaction was at log time 2013-07-30 16:23:02.11493-06 2013-07-30 16:26:48 MDT WARNING: page 476 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 493 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 1023 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 708 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 1075 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 590 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 832 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 1742 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 238 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 334 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 1131 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 434 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 772 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 259 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 498 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 948 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 1743 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 96 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT WARNING: page 559 of relation base/603188/199093492 did not exist 2013-07-30 16:26:48 MDT PANIC: WAL contains references to invalid pages 2013-07-30 16:26:48 MDT LOG: startup process (PID 17546) was terminated by signal 6: Abort trap 2013-07-30 16:26:48 MDT LOG: aborting startup due to startup process failure Slave log: 2013-07-30 16:41:48 MDT FATAL: could not connect to the primary server: could not connect to server: Operation timed out Is the server running on host 192.168.131.1 and accepting TCP/IP connections on port 5432? 2013-07-30 16:42:59 MDT LOG: trigger file found: /pgdata/data/failover 2013-07-30
Re: [ADMIN] Help with PITR in PostgreSQL 8.4
On 7/17/13 10:40 AM, Nestor A. Diaz wrote: Hello Nestor I use its: standby_mode = 'on' primary_conninfo = 'host=X.X.X.X port=5432 user= password=YTT' trigger_file = '/var/pgsql/data/pg_failover_trigger' restore_command = 'cp /wal/%f /var/lib/postgresql/8.4/main/%p' Ok, but those instructions are for 9.1 not for 8.4, I would like to be sure if there is any way to perform countinus archiving then recovery then backup and loop over this every day whithout having to rsync every time I recover the standby database (8.4) Slds AFAIK The only way to do this in 8.4 is to make a copy of the standby and bring the copy online. We do it to run backups off the standby like this: 1) take the standby down (pg_ctl -m fast stop) 2) rsync the standby to the standby-copy 3) restart the standby (it will return to recovery mode) 4) bring the standby copy online (remove it's recovery.conf and start it up) 5) run the backups on the copy 6) shutdown the copy (but leave it in place so the next rsync only has to copy changed data) 7) rinse and repeat -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] HOT Standby - slave does not appear to be removing wal files
All; We're doing a migration as follows: 1) we setup a HOT standby pair 2) we did an import of a db that wan backed up via pg_dump The pg_xlog directory has 1700 files in it, It seems that the slave is way behind which I get since we just imported a 55GB database but the slave is not removing it's local pg_xlog wal files. We actually stopped the import when the slave hit 97% full for the file system where we have it's pg_xlog directory mounted after like 30minutes the file count in the slave's pg_xlog dir has not changed. Any thoughts per why the slave is not removing these files? Thanks in advance -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] SSL question
All; We have a client that is asking for a service as follows: - a virtual machine in our data center - they want to upload files to the VM via sftp - they want to connect to a local db server on the VM via SSL Here's my question; Do I need to pay for an official SSL cert? If so, they seem to be tied to an apache config. Am I missing something? Thanks in advance -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] restoring from a dump
On 1/5/13 1:54 PM, J Rouse wrote: Stupid person here. Just got PostgresSQL today. Have text dump file. Need to restore. I think I need to use psql, but do not even know how to get to it. Need exact instructions on where to enter the command line, setting up paths if necessary, etc. Jim to restore from a text (sql) file: 1) ensure you have a target database: - you might need to set your path, i.e. you need to know where psql is - if you did an rpm / package install it's probably in /usr/bin # connect to the cluster (database instance): $ psql # then create your target database: postgres# create database my_target_db; Then exit from psql (exit; or ctl-d) postgres# exit; 2) import the sql text file - cd to the directory where the text file is: $ cd /home/postgres/Downloads - Import the file $ psql -ef ./import_file.sql my_target_db You could also point to the sql file without being in the directory: $ psql -ef /home/postgres/Downloads/import_file.sql my_target_db Note the -f is the psql flag to import (or run) a specified sql file from the command line, the -e flag says to push all SQL statements as well as normal output to STDOUT See the psql command docs for more info here: http://www.postgresql.org/docs/9.2/interactive/app-psql.html Hope this helps, /Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] 9.2.2 build error
Hi all; I'm building 9.2.2 from source on a Scientific Linux 6.2 box. I downloaded the tar.bz2 file from here: http://www.postgresql.org/ftp/source/v9.2.2/ I unzipped the file and ran this: ./configure --prefix=/usr/local/pgsql --with-perl --with-openssl then I get this error: checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .). BEGIN failed--compilation aborted. no configure: error: could not determine flags for linking embedded Perl. This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not installed. I installed these packages: yum install perl-ExtUtils-MakeMaker perl-ExtUtils-Embed Still get the same error. Next I installed this: yum install perl-ExtUtils* which installed this: Installing: perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker-Coverage Installing for dependencies: perl-Algorithm-Diff perl-AppConfig perl-Devel-Cover perl-Devel-Symdump perl-Locale-Maketext-Simple perl-Object-Accessor perl-Params-Check perl-Pod-Coverage perl-Pod-POM perl-Template-Toolkit perl-Test-Differences perl-Text-Diff I still get the above error. Note that the error says Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) However ExtUtils/Embed.pm is here: # find / -name Embed.pm /usr/share/perl5/ExtUtils/Embed.pm Thanks in advance for any help. /Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] 9.2.2 build error (SOLVED)
On 1/5/13 10:58 AM, CS DBA wrote: Hi all; I'm building 9.2.2 from source on a Scientific Linux 6.2 box. I downloaded the tar.bz2 file from here: http://www.postgresql.org/ftp/source/v9.2.2/ I unzipped the file and ran this: ./configure --prefix=/usr/local/pgsql --with-perl --with-openssl then I get this error: checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .). BEGIN failed--compilation aborted. no configure: error: could not determine flags for linking embedded Perl. This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not installed. I installed these packages: yum install perl-ExtUtils-MakeMaker perl-ExtUtils-Embed Still get the same error. Next I installed this: yum install perl-ExtUtils* which installed this: Installing: perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker-Coverage Installing for dependencies: perl-Algorithm-Diff perl-AppConfig perl-Devel-Cover perl-Devel-Symdump perl-Locale-Maketext-Simple perl-Object-Accessor perl-Params-Check perl-Pod-Coverage perl-Pod-POM perl-Template-Toolkit perl-Test-Differences perl-Text-Diff I still get the above error. Note that the error says Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) However ExtUtils/Embed.pm is here: # find / -name Embed.pm /usr/share/perl5/ExtUtils/Embed.pm Thanks in advance for any help. /Kevin USER ERROR. Nevermind -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] System / Global psqlrc file location
Hi all; where does the system psqlrc file live? Thanks in advance -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Canot access PostgreSQL via psql -h (Vmware Fusion)
Hi all; I've fired up 2 CentOS 6.2 VM's via vmware fusion 5 (on a mac). I disables selinux on both, and installed postgres 8.4.13 on both VM's I set listen_addresses = '*' and I added a trust entry for each server in the opposite server's pg_hba.conf file. However I cannot access one server from the other one via psql -h I.P. address I get the standard error: psql -h 192.168.91.145 psql: could not connect to server: No route to host Is the server running on host 192.168.91.145 and accepting TCP/IP connections on port 5432? I can ssh between servers, I see no entry in the postgres log per the connection attempt (I have log_connections set to on) Currently I have networking set to share with my mac or NAT. I tried setting networking to Private to my mac with the same results. Tried Autodetect (Bridged) as well, no luck Can anyone help me debug this? Thanks in advance -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] FATAL: shmat(id=4096103) failed: Cannot allocate memory
Hi All; I just installed Fedora 16, I was running fedora 14. I installed Postgresql from source and set SHMMAX to 2198066816 I set postgres to use 2GB for shared_buffers and I get this when I try to start: FATAL: shmat(id=4096103) failed: Cannot allocate memory The only real difference from Fedora 14 is that on Fedora 16 I'm using the PAE kernel Here's my current SHM kernel settings: # sysctl -a | grep -i shm kernel.shmmax = 2198066816 kernel.shmall = 2198066816 kernel.shmmni = 4096 kernel.shm_rmid_forced = 0 vm.hugetlb_shm_group = 0 Thoughts? Thanks in advance -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Read Only Role
On 11/30/2011 05:05 PM, M. D. wrote: On 11/29/2011 11:52 PM, senthilnathan wrote: Is there any simple way to define read only roles. Basically that user should be able to login and be able to do *select* statements alone. It should be applied for all tables in all schemas in a database. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Read-Only-Role-tp5034628p5034628.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. Google is your friend: http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql I think you can do this: ALTER USER [user] SET TRANSACTION READ ONLY; -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replication from Oracle to PostgreSQL?
On 08/11/2011 08:09 PM, Craig Ringer wrote: On 11/08/2011 10:57 PM, CS DBA wrote: On Thu, 2011-08-11 at 08:41 -0600, CS DBA wrote: Anyone know of tools / options that will allow Oracle to PostgreSQL replication? or at least a real time feed or dblink? EnterpriseDB's Postgres Plus Advanced Server has a realtime replication solution bundled. Regards, That was the first thing I tried to push 'em towards... they shut it down cause it's not free (which is weird since they're not considering a real TCO, but they are the customer). Using Oracle ... and objecting to not free? That's why we're here - to help 'em migrate from Oracle to PostgreSQL, it seems some systems will need a longer Oracle life due to other constraints *boggle* agreed There are lots of ETL tools available, and there's always the roll-your-own queue-based trigger replication system option. Of course, both options would probably cost more than buying EDB's already built and tested version... interesting idea, any specific ETL tools you could recommend? -- Craig Ringer -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Master to Master replication options?
Hi All; I have a client that wants the following: - 2 masters (master to master replication) - multiple read only slaves off each master - the ability to failover in case of a master node failure to include re-directing the slaves off the failed master to now point to the remaining active master Questions: - Is Bucardo the only viable master to master solution? - Can I setup HOT standby's or SLONY off of a Bucardo master? - Can I shift a slave's 'master' without a full reset? Thanks in advance... -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] replication from Oracle to PostgreSQL?
Hi All; Anyone know of tools / options that will allow Oracle to PostgreSQL replication? or at least a real time feed or dblink? Thanks in advance... -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replication from Oracle to PostgreSQL?
On Thu, 2011-08-11 at 08:41 -0600, CS DBA wrote: Anyone know of tools / options that will allow Oracle to PostgreSQL replication? or at least a real time feed or dblink? EnterpriseDB's Postgres Plus Advanced Server has a realtime replication solution bundled. Regards, That was the first thing I tried to push 'em towards... they shut it down cause it's not free (which is weird since they're not considering a real TCO, but they are the customer). -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] PostgreSQL fails to bind IPv6 socket at startup on AIX platform
Hi All; we're trying to get PostgreSQL configured on an AIX box (AIX version 5). After some digging adding various paths to LD_LIBRARY_PATH (Thanks to help from Tom Lane) we were able to complete the source based build. However, when we try and start the db we get the following: $ pg_ctl start server starting $ LOG: could not bind IPv6 socket: The socket name is already in use. HINT: Is another postmaster already running on port 3116? If not, wait a few seconds and retry. I've verified via 'ps' that there are no postgres processes running, also there is no postmaster.pid file present in the $PGDATA dir Thoughts? Thanks in advance -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] initdb fails on AIX
Hi All; We're getting the following error when we try to run initdb on an AIX box: fgets failure: Error 0 The program postgres is needed by initdb but was not found in the same directory as /opt/app/postgres/904/bin/initdb. Check your installation. We did this: 1) installed (via source) postgresql 9.0.4 using the following parameters for the configure command: --prefix=opt/app/postgres/904 --with-openssl 2) added opt/app/postgres/904 to the current PATH 3) exported PGDATA=/pgdata/newdb 4) ran initdb Note: we also ran initdb -D /pgdata/newdb with the same results Thoughts? -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] initdb fails on AIX
On 08/10/2011 01:16 PM, Tom Lane wrote: CS DBAcs_...@consistentstate.com writes: We're getting the following error when we try to run initdb on an AIX box: fgets failure: Error 0 The program postgres is needed by initdb but was not found in the same directory as /opt/app/postgres/904/bin/initdb. Check your installation. This means that initdb tried to execute postgres -V and didn't get any output. What happens when you try that directly? regards, tom lane See below. One oddity, if I compile without the --with-openssl flag (for the configure command) then it works fine. Also, I forgot to mention that we are having to add the --disable-thread-safety flag or the configure command fails complaining that the platform is not thread safe - I suspect this is not the issue but figured I should mention it just in case... $ ./postgres -V exec(): 0509-036 Cannot load program ./postgres because of the following errors: 0509-150 Dependent module libcrypto.a(libcrypto.so.0.9.7) could not be loaded. 0509-022 Cannot load module libcrypto.a(libcrypto.so.0.9.7). 0509-026 System error: A file or directory in the path name does not exist. -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] initdb fails on AIX
On 08/10/2011 02:22 PM, Tom Lane wrote: CS DBAcs_...@consistentstate.com writes: On 08/10/2011 01:16 PM, Tom Lane wrote: This means that initdb tried to execute postgres -V and didn't get any output. What happens when you try that directly? See below. One oddity, if I compile without the --with-openssl flag (for the configure command) then it works fine. exec(): 0509-036 Cannot load program ./postgres because of the following errors: 0509-150 Dependent module libcrypto.a(libcrypto.so.0.9.7) could not be loaded. 0509-022 Cannot load module libcrypto.a(libcrypto.so.0.9.7). 0509-026 System error: A file or directory in the path name does not exist. Well, yeah. It looks like there is something pretty broken about your openssl installation. At the very least you need a LD_LIBRARY_PATH (or local equivalent) pointing to wherever libcrypto.so is hiding ... but a reasonable installation of openssl should have taken care of that for you. Possibly you could build postgres with an rpath (or local equivalent) pointing to openssl's library directory. regards, tom lane Thanks, I'll see if I can track down the libcrypto location and try tweaking LD_LIBRARY_PATH first.. Thanks again for the help. /Kevin -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] what is a serial_fkey data type?
nevermind, domain type I'm seeing a number of serial_fkey data types in one of our databases (postgresql 8.4) but I dont find this data type in the docs. Where does this come from? Thanks in advance -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] what is a serial_fkey data type?
I'm seeing a number of serial_fkey data types in one of our databases (postgresql 8.4) but I dont find this data type in the docs. Where does this come from? Thanks in advance -- -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] finding the procpid (or pid) for a prepared transaction
Hi All; I have several prepared tx which are running for a long long time. I want to kill 'em but I dont know the process id. I can get the xid via pg_prepared_xacts.transaction however the tx's have no locks thus no corresponding pg_locks row. Anyone know how I can get a procpid based on a tx id? Thanks in advance... -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] finding the procpid (or pid) for a prepared transaction
On 02/17/2011 07:19 PM, Tom Lane wrote: CS DBAcs_...@consistentstate.com writes: I have several prepared tx which are running for a long long time. I want to kill 'em but I dont know the process id. If they're prepared, they're *not* running, and they don't have a process id. They're just going to sit there until you do COMMIT PREPARED or ROLLBACK PREPARED. regards, tom lane aah, makes sense Thanks. So, for future reference.. if I prepare a tx, and then execute the prepared stmt, is there a way to associate the xid from a prepared txn (pg_prepared_xacts) with the row in pg_stat_activity showing the executing prepared stmt (within the current prepared txn)? or is there a way to see the text of a currently prepared txn so I can look for the statement being executed in pg_stat_activity? -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] finding the procpid (or pid) for a prepared transaction
On 02/17/2011 07:48 PM, CS DBA wrote: On 02/17/2011 07:19 PM, Tom Lane wrote: CS DBAcs_...@consistentstate.com writes: I have several prepared tx which are running for a long long time. I want to kill 'em but I dont know the process id. If they're prepared, they're *not* running, and they don't have a process id. They're just going to sit there until you do COMMIT PREPARED or ROLLBACK PREPARED. regards, tom lane aah, makes sense Thanks. So, for future reference.. if I prepare a tx, and then execute the prepared stmt, is there a way to associate the xid from a prepared txn (pg_prepared_xacts) with the row in pg_stat_activity showing the executing prepared stmt (within the current prepared txn)? or is there a way to see the text of a currently prepared txn so I can look for the statement being executed in pg_stat_activity? nevermind sorry I'm confusing prepared txn's with prepared stmts. -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres on NAS/NFS
On 02/09/2011 03:49 PM, Jim Mlodgenski wrote: On Wed, Feb 9, 2011 at 2:59 PM, Bryan Kellerbrya...@gmail.com wrote: I am considering running a Postgres with the database hosted on a NAS via NFS. I have read a few things on the Web saying this is not recommended, as it will be slow and could potentially cause data corruption. My goal is to have the database on a shared filesystem so in case of server failure, I can start up a standby Postgres server and point it to the same database. I would rather not use a SAN as I have heard horror stories about managing them. Also they are extremely expensive. A DAS would be another option, but I'm not sure if a DAS can be connected to two servers for server failover purposes. Currently I am considering not using a shared filesystem and instead using replication between the two servers. I am wondering what solutions have others used for my active-passive Postgres failover scenario? Is a NAS still not a recommended approach? Will a DAS work? Or is replication the best approach? DAS will absolutely work. Just be careful to fence things properly so that you don't end up with 2 servers trying to start the data directory at the same time. It will lead to some pretty nasty corruption. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin Jim is right, be very careful to fence things properly. Also, depending on how the NAS/DAS is mounted to the servers you may need to tweak permissions. I've seen NFS scenarios where the postgres user on the secondary machine has a different UID than the one on the source box and the result was a mount point owned by nobody:nobody which of course disallows postgres from accessing the file system. -- - Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com - -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres Replication Options
One of the main considerations per Hot Standby vs SLONY is replication scope. With Hot Standby you get everything that occurs in the cluster, across all databases. With SLONY you are limited to at most a single database per SLONY Cluster, and you can define replication sets which only contain a sub-set of the tables in the database. So, IMHO I'd go with Hot Standby if I wanted to replicate the full cluster and SLONY if I wanted to slice dice tables and target slaves (i.e. replicate all tables for a single db to slave 1, only 50 tables to slave 2, etc...) --hope this helps /Kevin Hi, Il 09/02/11 01:34, Rangi, Jai ha scritto: Hello, I am looking for a replication solution for PG 9.x. Idea is to have one master replication server and multiple (around 20) slave servers read only. I see PG 9 has inbuilt Streaming replication. Is this the best replication solution. How about slony? Which option will keep the slave nodes in closest sync to master and which is more stable. With PostgreSQL 9.0 in terms of builtin replication you can take advantage of Hot Standby based on either WAL shipping or on Streaming Replication.I would personally stick with builtin solutions if you plan to have full replicas of your databases, even in terms of maintenance and upgrades later on (in the long term). Some tools, including replication manager (repmgr) are coming out in order to manage HA clusters (for more info: http://blog.2ndquadrant.com/en/2011/01/easier-postgresql-90-clusters.html). Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it |www.2ndQuadrant.it
[ADMIN] State of multi-master replication options
Hi All; I suspect I know the answer to this... What's the current state of multi-master replication for PostgreSQL? Is Bucardo the only true master/master solution out there that might be worthy of a production push? Is Postres-R a candidate at this point (I suspect not)? Are there any other master/master or preferably multi-master (3+ masters) solutions available? Thanks in advance... -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
