Re: [GENERAL] empty pg_stat_replication when replication works fine?
I've found the reason: inconsistent catalog data. This state did not disappear after restart of all postgres instances. pg_authid.oid does not match the one in pg_stat_get_activity(NULL::integer). I wonder how this inconsistency could happen.. Maybe some error during cloning database (binary database copy, or some older WAL logs left over either in archive or pg_xlog, or some corrupted index in pg_catalog?) during several test-cycles.. Any other idea how can oid of users could be different from the one appearing in pg_stat_get_activity()? (see details below) Andrej --details: postgres=# \d+ pg_stat_replication; View "pg_catalog.pg_stat_replication" Column | Type | Modifiers | Storage | Description --+--+---+--+- pid | integer | | plain| usesysid | oid | | plain| usename | name | | plain| application_name | text | | extended | client_addr | inet | | main | client_hostname | text | | extended | client_port | integer | | plain| backend_start| timestamp with time zone | | plain| state| text | | extended | sent_location| text | | extended | write_location | text | | extended | flush_location | text | | extended | replay_location | text | | extended | sync_priority| integer | | plain| sync_state | text | | extended | View definition: SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE s.usesysid = u.oid AND s.pid = w.pid; postgres=# \dv+ pg_stat_replication; List of relations Schema |Name | Type | Owner | Size | Description +-+--+--+-+- pg_catalog | pg_stat_replication | view | postgres | 0 bytes | (1 row) postgres=# select * from pg_stat_get_wal_senders(); pid | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state -+---+---+++-+---+ 707 | streaming | 0/B590| 0/B590 | 0/B590 | 0/B590 | 0 | async (1 row) postgres=# select * from pg_stat_get_activity(NULL::integer); datid | pid | usesysid | application_name | state | query| waiting | xact_start | query_star t | backend_start | state_change | client_addr | client_hostname | client_port ---+-+--+--+++-+---+ ---+---+---++-+- 0 | 707 |34456 | l2amain | idle | | f | | | 2016-05-31 13:19:04.875468+02 | 2016-05-31 13:19:04.877894+02 | 192.168.101.11 | | 33329 12896 | 709 | 10 | psql | active | select * from pg_stat_get_activity(NULL::integer); | f | 2016-05-31 13:22:23.090373+02 | 2016-05-31 13:22:23 .090373+02 | 2016-05-31 13:19:08.719215+02 | 2016-05-31 13:22:23.090382+02 || | -1 (2 rows) postgres=# select * from pg_authid where oid = 34456; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil -+--++---+-+--+-++--+-+--- (0 rows) postgres=# select oid, * from pg_authid; oid | rolname | rolsuper | rolinherit | rol
Re: [GENERAL] empty pg_stat_replication when replication works fine?
The instance is still running, I tried to collect more information from it: all databases are working as expected, the only issue is that monitoring SQL commands (pg_stat_activity, pg_stat_replication) are not working as expected (do not reflect postgres processes list from command-line) on Master: - pg_stat_activity is empty as well (they can be seen just in ps f -fu postgres output: CTSYSTEM lines) - psql as postgres: select * from pg_stat_activity sees only its own session - psql as unprivileged user (CTSYSTEM): select * from pg_stat_activity is empty - replication works fine (created a table, that was created also on all replicas) - added lines to postgresql.conf + reload: Opening new lines to postgresql.conf + reload configuration: client_min_messages = debug5 log_min_messages = debug5 log_min_error_statement = debug5 - activity seen in pg_log, also replication activity (pgreplic user) is seen, still nothing in pg_stat_replication/pg_stat_activity killed one slave postgres instance, restarted it - "standby "l2abrnch" has now caught up with primary" - replication works fine - no entries on Master in pg_stat_replication - ps -ef shows the new wal-sender process on master and wal-receiver process streaming on this slave Version is: PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit I suspect something happened within master server (pg_stat_activity+pg_stat_replication not working as described, do not reflect ps -ef list of postgres processes and running SQL client/replication information) What may be additionally useful information before restarting the master? Regards, Andrej 2016-05-25 23:22 GMT+02:00 Andrej Vanek <andrej.vanek...@gmail.com>: > Streaming replication set-up, > > one master, 3 slaves connecting to it. > I expected ps -ef gets all wal-sender processes and SAME information I'll > get via select * from pg_stat_replication. > Instead I observed: > - pg_stat_replication is empty > - 3 wal-sender processes up and running > - each slave has wal-receiver process running > - replication works (tried to create a table- it appears in all databases) > Question: > - why is pg_stat_replication empty? > > Andrej > ---details > [root@l2bmain ~]# tail /opt/pg_data/postgresql.conf > max_wal_senders = 5 > hot_standby = on > wal_keep_segments = 128 > archive_command = '/opt/postgres/dbconf/archive_command.sh %p %f' > wal_receiver_status_interval = 2 > max_standby_streaming_delay = -1 > max_standby_archive_delay = -1 > restart_after_crash = off > hot_standby_feedback = on > wal_sender_timeout = 1min > [root@l2bmain ~]# ps f -fu postgres > UIDPID PPID C STIME TTY STAT TIME CMD > postgres 10797 1 0 15:53 ?S 0:20 > /usr/pgsql-9.3/bin/postgres -D /opt/pg_data -c > config_file=/opt/pg_data//postgresql.conf > postgres 10820 10797 0 15:53 ?Ss 0:00 \_ postgres: logger > process > postgres 10823 10797 0 15:53 ?Ss 0:00 \_ postgres: > checkpointer process > postgres 10824 10797 0 15:53 ?Ss 0:00 \_ postgres: writer > process > postgres 10825 10797 0 15:53 ?Ss 0:00 \_ postgres: wal > writer process > postgres 10826 10797 0 15:53 ?Ss 0:01 \_ postgres: > autovacuum launcher process > postgres 10827 10797 0 15:53 ?Ss 0:00 \_ postgres: archiver > process last was 000100A3.0028.backup > postgres 10828 10797 0 15:53 ?Ss 0:03 \_ postgres: stats > collector process > postgres 11286 10797 0 15:54 ?Ss 0:08 \_ postgres: wal > sender process pgreplic 192.168.204.12(55231) streaming 0/A401BED8 > postgres 11287 10797 0 15:54 ?Ss 0:06 \_ postgres: wal > sender process pgreplic 192.168.204.11(42937) streaming 0/A401BED8 > postgres 19322 10797 0 15:58 ?Ss 0:08 \_ postgres: wal > sender process pgreplic 192.168.101.11(52379) streaming 0/A401BED8 > postgres 28704 10797 0 18:44 ?Ss 0:00 \_ postgres: CTSYSTEM > lidb 192.168.102.13(58245) idle > postgres 7256 10797 0 18:52 ?Ss 0:00 \_ postgres: CTSYSTEM > lidb 192.168.102.23(55190) idle > postgres 8667 10797 0 18:53 ?Ss 0:00 \_ postgres: CTSYSTEM > lidb 192.168.102.13(58287) idle > [root@l2bmain ~]# psql -U postgres -c "select * from pg_stat_replication;" > pid | usesysid | usename | application_name | client_addr | > client_hostname | client_port | backend_start | state | sent_location | > write_location | flush_location | r > eplay_location | sync_priority | sync_state > > -+--+-+--+-+-+-+---+---+---+++-- &g
[GENERAL] empty pg_stat_replication when replication works fine?
Streaming replication set-up, one master, 3 slaves connecting to it. I expected ps -ef gets all wal-sender processes and SAME information I'll get via select * from pg_stat_replication. Instead I observed: - pg_stat_replication is empty - 3 wal-sender processes up and running - each slave has wal-receiver process running - replication works (tried to create a table- it appears in all databases) Question: - why is pg_stat_replication empty? Andrej ---details [root@l2bmain ~]# tail /opt/pg_data/postgresql.conf max_wal_senders = 5 hot_standby = on wal_keep_segments = 128 archive_command = '/opt/postgres/dbconf/archive_command.sh %p %f' wal_receiver_status_interval = 2 max_standby_streaming_delay = -1 max_standby_archive_delay = -1 restart_after_crash = off hot_standby_feedback = on wal_sender_timeout = 1min [root@l2bmain ~]# ps f -fu postgres UIDPID PPID C STIME TTY STAT TIME CMD postgres 10797 1 0 15:53 ?S 0:20 /usr/pgsql-9.3/bin/postgres -D /opt/pg_data -c config_file=/opt/pg_data//postgresql.conf postgres 10820 10797 0 15:53 ?Ss 0:00 \_ postgres: logger process postgres 10823 10797 0 15:53 ?Ss 0:00 \_ postgres: checkpointer process postgres 10824 10797 0 15:53 ?Ss 0:00 \_ postgres: writer process postgres 10825 10797 0 15:53 ?Ss 0:00 \_ postgres: wal writer process postgres 10826 10797 0 15:53 ?Ss 0:01 \_ postgres: autovacuum launcher process postgres 10827 10797 0 15:53 ?Ss 0:00 \_ postgres: archiver process last was 000100A3.0028.backup postgres 10828 10797 0 15:53 ?Ss 0:03 \_ postgres: stats collector process postgres 11286 10797 0 15:54 ?Ss 0:08 \_ postgres: wal sender process pgreplic 192.168.204.12(55231) streaming 0/A401BED8 postgres 11287 10797 0 15:54 ?Ss 0:06 \_ postgres: wal sender process pgreplic 192.168.204.11(42937) streaming 0/A401BED8 postgres 19322 10797 0 15:58 ?Ss 0:08 \_ postgres: wal sender process pgreplic 192.168.101.11(52379) streaming 0/A401BED8 postgres 28704 10797 0 18:44 ?Ss 0:00 \_ postgres: CTSYSTEM lidb 192.168.102.13(58245) idle postgres 7256 10797 0 18:52 ?Ss 0:00 \_ postgres: CTSYSTEM lidb 192.168.102.23(55190) idle postgres 8667 10797 0 18:53 ?Ss 0:00 \_ postgres: CTSYSTEM lidb 192.168.102.13(58287) idle [root@l2bmain ~]# psql -U postgres -c "select * from pg_stat_replication;" pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | r eplay_location | sync_priority | sync_state -+--+-+--+-+-+-+---+---+---+++-- ---+---+ (0 rows) [root@l2bmain ~]# tail /opt/pg_data/pg_log/postgresql-Wed.log 2016-05-25 15:53:56 CEST:@:[8603] LOG: database system is shut down 2016-05-25 15:53:58 CEST:@:[10821] LOG: database system was shut down in recovery at 2016-05-25 15:53:56 CEST 2016-05-25 15:53:58 CEST:@:[10821] LOG: database system was not properly shut down; automatic recovery in progress 2016-05-25 15:53:58 CEST:@:[10821] LOG: consistent recovery state reached at 0/A290 2016-05-25 15:53:58 CEST:@:[10821] LOG: record with zero length at 0/A290 2016-05-25 15:53:58 CEST:@:[10821] LOG: redo is not required 2016-05-25 15:53:58 CEST:@:[10821] LOG: MultiXact member wraparound protections are now enabled 2016-05-25 15:53:58 CEST:@:[10797] LOG: database system is ready to accept connections 2016-05-25 15:53:58 CEST:@:[10826] LOG: autovacuum launcher started ` [root@l2bmain ~]# ssh 192.168.101.11 Last login: Wed May 25 22:48:18 2016 from 192.168.101.12 [root@l2amain ~]# ps f -fu postgres UIDPID PPID C STIME TTY STAT TIME CMD postgres 5730 1 0 15:58 ?S 0:04 /usr/pgsql-9.3/bin/postgres -D /opt/pg_data -c config_file=/opt/pg_data//postgresql.conf postgres 5754 5730 0 15:58 ?Ss 0:00 \_ postgres: logger process postgres 5755 5730 0 15:58 ?Ss 0:00 \_ postgres: startup process recovering 000100A4 postgres 5773 5730 0 15:58 ?Ss 0:12 \_ postgres: wal receiver process streaming 0/A401C030 postgres 5774 5730 0 15:58 ?Ss 0:00 \_ postgres: checkpointer process postgres 5775 5730 0 15:58 ?Ss 0:00 \_ postgres: writer process postgres 5776 5730 0 15:58 ?Ss 0:00 \_ postgres: stats collector process [root@l2amain ~]# psql -U postgres -c "select pg_is_in_recovery();" pg_is_in_recovery --- t (1 row) [root@l2bmain ~]# ssh 192.168.204.11 Warning: Permanently added '192.168.204.11' (RSA) to the list of known hosts. Last login: Wed May 25 16:28:49 2016 from 192.168.200.254 [root@l
Re: [GENERAL] pg_basebackup: return value 1: reason?
Hello, I've given a try once again. Two variants used in my script (launched by crm_mon): 1. /usr/pgsql-9.5/bin/pg_basebackup -U pgreplic -h db-other-site -w -D /opt/geo_stdby_data -c fast -vvv -X stream &>> /tmp/log 2. strace -o /tmp/pg_basebackup.log /usr/pgsql-9.5/bin/pg_basebackup -U pgreplic -h db-other-site -w -D /opt/geo_stdby_data -c fast -vvv -X stream &>> /tmp/log Result: variant 2. works fine with return code 0 (with strace) variant 1. fails with error code 1 (without strace) Any ideas? Andrej --details Output: Variant 2: DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: DEBUG: received replication command: IDENTIFY_SYSTEM DEBUG: received replication command: BASE_BACKUP LABEL 'pg_basebackup base backup' FAST NOWAIT -- Mon May 23 17:54:31 CEST 2016 [l1abrnch->l1abrnch:3122/27282:GEO] --INFO-- l1abrnch->l1abrnch (GEO-STDBY-DB / stop: 0): target/returned 0/0 (OK) transaction log start point: 0/FA28 on timeline 1 pg_basebackup: starting background WAL receiver DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: DEBUG: received replication command: IDENTIFY_SYSTEM DEBUG: received replication command: START_REPLICATION 0/FA00 TIMELINE 1 WARNING: skipping special file "./pg_hba.conf" DEBUG: standby "pg_basebackup" has now caught up with primary DEBUG: write 0/FA00 flush 0/0 apply 0/0 DEBUG: removing transaction log backup history file "000100F8.0028.backup" transaction log end point: 0/FAF8 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed RETVAL=0 Output Variant 1: DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: DEBUG: received replication command: IDENTIFY_SYSTEM DEBUG: received replication command: BASE_BACKUP LABEL 'pg_basebackup base backup' FAST NOWAIT -- Mon May 23 17:55:32 CEST 2016 [l1abrnch->l1abrnch:3122/28785:GEO] --INFO-- l1abrnch->l1abrnch (GEO-STDBY-DB / stop: 0): target/returned 0/0 (OK) transaction log start point: 0/FC28 on timeline 1 pg_basebackup: starting background WAL receiver DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: DEBUG: received replication command: IDENTIFY_SYSTEM DEBUG: received replication command: START_REPLICATION 0/FC00 TIMELINE 1 WARNING: skipping special file "./pg_hba.conf" DEBUG: standby "pg_basebackup" has now caught up with primary DEBUG: write 0/FC00 flush 0/0 apply 0/0 DEBUG: removing transaction log backup history file "000100FA.0028.backup" transaction log end point: 0/FCF8 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: could not wait for child process: No child processes RETVAL=1 2016-04-18 16:12 GMT+02:00 Adrian Klaver <adrian.kla...@aklaver.com>: > On 04/17/2016 12:13 PM, Andrej Vanek wrote: > >> Hello Adrian, >> >> I tried to use -U without "su"- launched directly by root: same behaviour. >> Finally I reverted my script to use standard backup (pg_start_backup; >> rsync; pg_stop_backup)- this works- the only downside is possible >> collisions with on-line backup/synchronizaiton of other two nodes on >> master node... >> >> Back to the pg_basebackup issue: it is clear to me that this is an issue >> of environment which launched pg_basebackup. >> Possibly either some privileges or some kernel parameters/limits. Who >> knows? >> Summary: clusterlab's crm_mon launched a shell script starting >> pg_basebackup which fails to do some its work (pg_basebackup: could not >> wait for child process: No child processes)- probably due to some >> failing system call. >> >> How can I report to clusterlabs: What system call fails in pg_basebackup? >> > > All I can to do is point you at: > > > https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD > > >> Best Regards, Andrej >> >> >> >> 2016-04-17 1:09 GMT+02:00 Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@aklaver.com>>: >> >> >> Is the su - even necessary? >> >> pg_basebackup is a Postgres client program you can specify the user >> you want it to connect to using -U. >> >> Or do you need the script to run as postgres in order to get >> permissions on wherever you are creating the backup directory? >> >> ha
Re: [GENERAL] pg_basebackup: return value 1: reason?
Hello Adrian, I tried to use -U without "su"- launched directly by root: same behaviour. Finally I reverted my script to use standard backup (pg_start_backup; rsync; pg_stop_backup)- this works- the only downside is possible collisions with on-line backup/synchronizaiton of other two nodes on master node... Back to the pg_basebackup issue: it is clear to me that this is an issue of environment which launched pg_basebackup. Possibly either some privileges or some kernel parameters/limits. Who knows? Summary: clusterlab's crm_mon launched a shell script starting pg_basebackup which fails to do some its work (pg_basebackup: could not wait for child process: No child processes)- probably due to some failing system call. How can I report to clusterlabs: What system call fails in pg_basebackup? Best Regards, Andrej 2016-04-17 1:09 GMT+02:00 Adrian Klaver <adrian.kla...@aklaver.com>: > > Is the su - even necessary? > > pg_basebackup is a Postgres client program you can specify the user you > want it to connect to using -U. > > Or do you need the script to run as postgres in order to get permissions > on wherever you are creating the backup directory? > > have to find out why pg_basebackup cannot fork when launched from crm_mon. >> > > > I assume crm_mon is this: > > http://linux.die.net/man/8/crm_mon > > from Pacemaker. > > I do not use Pacemaker, but I am pretty sure that running what is a > monitoring program in daemon mode and then shelling out to another program > is not workable. The docs seem to bear this out: > > http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster#Installation > > > https://github.com/smbambling/pgsql_ha_cluster/wiki/Building-A-Highly-Available-Multi-Node-PostgreSQL-Cluster > >> >>
Re: [GENERAL] pg_basebackup: return value 1: reason?
Hello,
> Are you not getting something informative on your display and/or sending
stderr to somewher else?
- in case -X fetch no informative output
- in case -X stream following:
pg_basebackup: could not wait for child process: No child processes
Now after re-test directly from command-line it works. It fails just when
launched from a script fired by crm_mon -d -E my-script
Regards, Andrej
2016-04-16 1:18 GMT+02:00 Jerry Sievers <gsiever...@comcast.net>:
> Andrej Vanek <andrej.vanek...@gmail.com> writes:
>
> > Hello,
> >
> > I tried to run pg_basebackup. Return value is 1.
> >
> > How to find out its reason?
> > (I suspect that some wal after backup is missing- but how to find
> out the real reason? How to fix it?)
>
> Well there are more than 80 cases of exit 1 in a couple .c files and
> most/all are preceeded unsurprisingly with an fprintf(stderr.
>
> Are you not getting something informative on your display and/or sending
> stderr to somewher else?
>
> $ echo $*
> ./src/bin/pg_basebackup/pg_basebackup.c
> ./src/backend/replication/basebackup.c
>
> $ grep -h -B 4 -i 'exit.*(1' $* | egrep -hi 'fprintf\(stderr|exit'
> fprintf(stderr, _("%s: directory name too
> long\n"), progname);
> exit(1);
> fprintf(stderr, _("%s: multiple \"=\"
> signs in tablespace mapping\n"), progname);
> exit(1);
> fprintf(stderr,
> exit(1);
> fprintf(stderr, _("%s: old directory is not an absolute
> path in tablespace mapping: %s\n"),
> exit(1);
> fprintf(stderr, _("%s: new directory is not an absolute
> path in tablespace mapping: %s\n"),
> exit(1);
> fprintf(stderr, _("%s: could not read from
> ready pipe: %s\n"),
> exit(1);
> fprintf(stderr,
> exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr, _("%s: could not create background
> process: %s\n"),
> disconnect_and_exit(1);
> fprintf(stderr, _("%s: could not create background thread:
> %s\n"),
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr, _("%s: could not access directory
> \"%s\": %s\n"),
> disconnect_and_exit(1);
> fprintf(stderr,
> exit(1);
> fprintf(stderr,
> exit(1);
> fprintf(stderr, _("%s: transfer rate must be greater than
> zero\n"),
> exit(1);
> fprintf(stderr,
> exit(1);
> fprintf(stderr,
> exit(1);
> fprintf(stderr,
> exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr, _("%s: could not write to file
> \"%s\": %s\n"),
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr, _("%s: could not create file
> \"%s\": %s\n"),
> disconnect_and_exit(1);
> fprintf(stderr, _("%s: could not get COPY data stream:
> %s"),
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
> fprintf(stderr,
> disconnect_and_exit(1);
&g
Re: [GENERAL] pg_basebackup: return value 1: reason?
Hello,
my setup is:
1 master
1 synchronous slave (up and running)
2 asynchronous slave (up and running)
Now I'm setting up asynchronous slave: first step of this setup is
pg_basebackup connecting to master.
Indirections are because it is encapsulated in a script. This script is
aimed to serve for automated replication recovery. This script is launched
by crm_mon -d daemon.
You are right to check without indirections: now I tried to run the command
directly from command line without variables- it works fine...
This means: no problem in pg_basebackup itself
but some problem in environment of process launched from crm_mon daemon.
Thanks for your hint (remove indirections). Pg_basebackup works fine when
launched from command-line..
Unfortenutely I have still no clue how to solve the issue- su works. I have
to find out why pg_basebackup cannot fork when launched from crm_mon.
Best Regards, Andrej
2016-04-16 1:17 GMT+02:00 Adrian Klaver <adrian.kla...@aklaver.com>:
> On 04/15/2016 03:28 PM, Andrej Vanek wrote:
>
>> Hello,
>>
>> I tried to run pg_basebackup. Return value is 1.
>>
>> How to find out its reason?
>> (I suspect that some wal after backup is missing- but how to find
>> out the real reason? How to fix it?)
>>
>
> First it is not clear to me where you are taking the backup from, the
> master or the standby?
>
> Second there is a lot of redirection going on. What happens if you run the
> pg_basebackup directly (without doing su - postgres ...) and use hardcoded
> values instead of shell variables?
>
>
>
>> thanks, Andrej
>> --details:
>> environment: CentOS 6.7, postgres 9.5.1
>> ( PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
>> 20120313 (Red Hat 4.4.7-16), 64-bit)
>>
>> I tried 2 forms of pg_basebackup (-X fetch and -X stream). Both were
>> issued from a script:
>> # su - postgres -c "/usr/pgsql-9.5/bin/pg_basebackup -h ${DB_MASTER_IP}
>> -D ${GEO_STDBY_DATA} -U pgreplic -P -v -X fetch" 2>${LOG_FILE}.stderr
>> >> ${LOG_FILE}
>> # echo $?
>> 1 <--pg_basebackup failed!
>> # cat log.stderr
>> # cat /var/log/cluster/geo_repair.log.err
>> transaction log start point: 0/E328 on timeline 1
>> WARNING: skipping special file "./pg_hba.conf"
>> WARNING: skipping special file "./pg_hba.conf.save"
>> transaction log end point: 0/E3F8
>> pg_basebackup: base backup completed<--no
>> reason for pg_basebackup failure!
>> # cp /tmp/pg_hba.conf /tmp/postgresql.conf /pg_data/
>> # su - postgres -c "/usr/pgsql-9.5/bin/pg_ctl -D /pg_data/ start"
>> # tail /pg_data/pg_log/postgresql-Fri.log
>> `pg_xlog/000100E2' ->
>> `../backups/arc/000100E2'
>> 2016-04-15 23:15:10 CEST:pgreplic@[unknown]:[10667] WARNING: skipping
>> special file "./pg_hba.conf"
>> 2016-04-15 23:15:10 CEST:pgreplic@[unknown]:[10667] WARNING: skipping
>> special file "./pg_hba.conf.save" <---recorded in
>> pg_log on master node and copied by pg_basebackup (note time difference
>> between two servers)
>> 2016-04-15 23:15:02 CEST:@:[23321] LOG: database system was
>> interrupted; last known up at 2016-04-15 23:15:10 CEST
>> 2016-04-15 23:15:02 CEST:postgres@postgres:[23329] FATAL: the database
>> system is starting up
>> 2016-04-15 23:15:03 CEST:@:[23321] LOG: entering standby mode
>> 2016-04-15 23:15:03 CEST:@:[23321] LOG: database system was not
>> properly shut down; automatic recovery in progress <-something
>> missing from pg_basebackup
>> 2016-04-15 23:15:03 CEST:@:[23321] LOG: redo starts at 0/E328
>> 2016-04-15 23:15:03 CEST:@:[23321] LOG: consistent recovery state
>> reached at 0/E400
>> 2016-04-15 23:15:03 CEST:@:[23295] LOG: database system is ready to
>> accept read only connections
>> 2016-04-15 23:15:03 CEST:@:[23356] LOG: started streaming WAL from
>> primary at 0/E400 on timeline 1
>> ---second trial
>> # su - postgres -c "/usr/pgsql-9.5/bin/pg_basebackup -h ${DB_MASTER_IP}
>> -D ${GEO_STDBY_DATA} -U pgreplic -P -v -X stream"
>> # echo $?
>> 1
>> # cat /var/log/cluster/geo_repair.log.err
>> transaction log start point: 0/E528 on timeline 1
>> pg_basebackup: starting background WAL receiver
>> WARNING: skipping special file "./pg_hba.conf"
>> WARNING: skipping special file "./pg_hba.conf.save"
>> transaction log end point: 0/E5F8
>> pg_basebackup: waiting for background process to finish st
[GENERAL] pg_basebackup: return value 1: reason?
Hello,
I tried to run pg_basebackup. Return value is 1.
How to find out its reason?
(I suspect that some wal after backup is missing- but how to find out the
real reason? How to fix it?)
thanks, Andrej
--details:
environment: CentOS 6.7, postgres 9.5.1
( PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-16), 64-bit)
I tried 2 forms of pg_basebackup (-X fetch and -X stream). Both were issued
from a script:
# su - postgres -c "/usr/pgsql-9.5/bin/pg_basebackup -h ${DB_MASTER_IP} -D
${GEO_STDBY_DATA} -U pgreplic -P -v -X fetch" 2>${LOG_FILE}.stderr >>
${LOG_FILE}
# echo $?
1 <--pg_basebackup failed!
# cat log.stderr
# cat /var/log/cluster/geo_repair.log.err
transaction log start point: 0/E328 on timeline 1
WARNING: skipping special file "./pg_hba.conf"
WARNING: skipping special file "./pg_hba.conf.save"
transaction log end point: 0/E3F8
pg_basebackup: base backup completed<--no
reason for pg_basebackup failure!
# cp /tmp/pg_hba.conf /tmp/postgresql.conf /pg_data/
# su - postgres -c "/usr/pgsql-9.5/bin/pg_ctl -D /pg_data/ start"
# tail /pg_data/pg_log/postgresql-Fri.log
`pg_xlog/000100E2' ->
`../backups/arc/000100E2'
2016-04-15 23:15:10 CEST:pgreplic@[unknown]:[10667] WARNING: skipping
special file "./pg_hba.conf"
2016-04-15 23:15:10 CEST:pgreplic@[unknown]:[10667] WARNING: skipping
special file "./pg_hba.conf.save" <---recorded in
pg_log on master node and copied by pg_basebackup (note time difference
between two servers)
2016-04-15 23:15:02 CEST:@:[23321] LOG: database system was interrupted;
last known up at 2016-04-15 23:15:10 CEST
2016-04-15 23:15:02 CEST:postgres@postgres:[23329] FATAL: the database
system is starting up
2016-04-15 23:15:03 CEST:@:[23321] LOG: entering standby mode
2016-04-15 23:15:03 CEST:@:[23321] LOG: database system was not properly
shut down; automatic recovery in progress <-something missing from
pg_basebackup
2016-04-15 23:15:03 CEST:@:[23321] LOG: redo starts at 0/E328
2016-04-15 23:15:03 CEST:@:[23321] LOG: consistent recovery state reached
at 0/E400
2016-04-15 23:15:03 CEST:@:[23295] LOG: database system is ready to accept
read only connections
2016-04-15 23:15:03 CEST:@:[23356] LOG: started streaming WAL from primary
at 0/E400 on timeline 1
---second trial
# su - postgres -c "/usr/pgsql-9.5/bin/pg_basebackup -h ${DB_MASTER_IP} -D
${GEO_STDBY_DATA} -U pgreplic -P -v -X stream"
# echo $?
1
# cat /var/log/cluster/geo_repair.log.err
transaction log start point: 0/E528 on timeline 1
pg_basebackup: starting background WAL receiver
WARNING: skipping special file "./pg_hba.conf"
WARNING: skipping special file "./pg_hba.conf.save"
transaction log end point: 0/E5F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: could not wait for child process: No child processes
<what does this mean? I think it failed to start process to fetching
wal logs created during backup: but neither on master node neither on
pg_basebackup output here is any information about reason..
(max_wal_senders on master is 10: I see no reason to fail).
postgres logs:
`pg_xlog/000100E4' ->
`../backups/arc/000100E4'
2016-04-15 23:35:09 CEST:pgreplic@[unknown]:[29035] WARNING: skipping
special file "./pg_hba.conf"
2016-04-15 23:35:09 CEST:pgreplic@[unknown]:[29035] WARNING: skipping
special file "./pg_hba.conf.save"
2016-04-15 23:35:01 CEST:@:[28926] LOG: database system was interrupted;
last known up at 2016-04-15 23:35:09 CEST
2016-04-15 23:35:01 CEST:postgres@postgres:[28938] FATAL: the database
system is starting up
2016-04-15 23:35:02 CEST:@:[28926] LOG: entering standby mode
2016-04-15 23:35:02 CEST:@:[28926] LOG: database system was not properly
shut down; automatic recovery in progress <this means
something missing from pg_basebackup
2016-04-15 23:35:02 CEST:@:[28926] LOG: redo starts at 0/E528
2016-04-15 23:35:02 CEST:@:[28926] LOG: consistent recovery state reached
at 0/E600
2016-04-15 23:35:02 CEST:@:[28904] LOG: database system is ready to accept
read only connections
2016-04-15 23:35:02 CEST:@:[28989] LOG: started streaming WAL from primary
at 0/E600 on timeline 1
postgres params on master node:
log_line_prefix = '%t:%u@%d:[%p] '
logging_collector = on
wal_buffers = 16MB
max_wal_size = 200MB
log_temp_files = 1MB
max_connections = 170
shared_buffers = 512MB
effective_cache_size = 1500MB
work_mem = 48MB
log_lock_waits = on
log_min_duration_statement = 1
shared_preload_libraries = 'pg_stat_statements'
include '/var/lib/pgsql/tmp/rep_mode.conf' # added by pgsql RA
wal_level = hot_standby
archive_mode = on
max_wal_senders =
Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement
Hi, retested: yes, this is still an issue in 9.3.5, same deadlock errors occured. Do you need to extract some simplified reproducible testcase? Best Regards, Andrej
Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement
Hi Bill, thanks for your answer. most often caused by something earlier in the transactions need all of the statements in each transaction It would be great if we could reveal an application error. Whole transactions I've already posted (in postgres log: log_min_duration_statement=0). Nothing suspicious yet: - both sessions COMMIT/ROLLBACK before BEGIN - both sessions run the same SQL statements - deadlock: FIRST statement of both transactions - deadlock: update single table, same row, column without any constraints (WHY?) - no statements of other sessions executed in between Any idea? thanks, Andrej
Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement
Hi, now I've checked release-notes of 9.3.5 (my version 9.3.4)- found a fix which probably could lead to my deadlocks: Fix race condition when updating a tuple concurrently locked by another process (Andres Freund,Álvaro Herrera) How can I make sure I've run into this bug?
[GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement
Hi, My application runs many concurrent sessions with the same transaction code starting with an update statement. I would expect locking and serialization of those transactions. But I get unexpected deadlocks. As opposed to *http://momjian.us/main/writings/pgsql/locking.pdf http://momjian.us/main/writings/pgsql/locking.pdf* page 84 (waits for ShareLock on transaction only) my case looks different: ERROR: deadlock detected DETAIL: Process 6973 waits for ShareLock on transaction 318396117; blocked by process 11039. ^^ Process 11039 waits for ExclusiveLock on tuple (4,9) of relation 16416 of database 16417; blocked by process 6973. ^^ Process 6973: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') Process 11039: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') Is this an expected behaviour, or maybe a bug? Thanks, Andrej ---details case is reproducible via my application. I was unable to reproduce it via psql sessions (locking worked fine). I was unable to reproduce it via shell for loop with psql sessions running same transactions (100 loops in 10 terminal windows). postgres version: PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit postgres log: 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.021 ms execute S_2: ROLLBACK 2014-09-22 22:21:54 CEST:yy@xx1:[11039] LOG: duration: 7.965 ms execute S_3: COMMIT 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.015 ms bind S_1: BEGIN 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.008 ms execute S_1: BEGIN 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.122 ms parse unnamed: select * from AELDATA_READ_VIEW where (SBO_GRP = '15') 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.317 ms bind unnamed: select * from AELDATA_READ_VIEW where (SBO_GRP = '15') 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.041 ms execute unnamed: select * from AELDATA_READ_VIEW where (SBO_GRP = '15') 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.017 ms bind S_2: ROLLBACK 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.022 ms execute S_2: ROLLBACK 2014-09-22 22:21:54 CEST:yy@xx1:[11039] LOG: duration: 0.017 ms bind S_1: BEGIN 2014-09-22 22:21:54 CEST:yy@xx1:[11039] LOG: duration: 0.010 ms execute S_1: BEGIN 2014-09-22 22:21:54 CEST:yy@xx1:[11039] LOG: duration: 0.083 ms parse unnamed: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.016 ms bind S_1: BEGIN 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.013 ms execute S_1: BEGIN 2014-09-22 22:21:54 CEST:yy@xx1:[11039] LOG: duration: 0.174 ms bind unnamed: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.096 ms parse unnamed: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:54 CEST:yy@xx1:[6973] LOG: duration: 0.152 ms bind unnamed: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:55 CEST:yy@xx1:[6973] LOG: process 6973 detected deadlock while waiting for ShareLock on transaction 318396117 after 1000.060 ms 2014-09-22 22:21:55 CEST:yy@xx1:[6973] STATEMENT: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:55 CEST:yy@xx1:[11039] LOG: process 11039 still waiting for ExclusiveLock on tuple (4,9) of relation 16416 of database 16417 after 1000.038 ms 2014-09-22 22:21:55 CEST:yy@xx1:[11039] STATEMENT: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:55 CEST:yy@xx1:[6973] ERROR: deadlock detected 2014-09-22 22:21:55 CEST:yy@xx1:[6973] DETAIL: Process 6973 waits for ShareLock on transaction 318396117; blocked by process 11039. Process 11039 waits for ExclusiveLock on tuple (4,9) of relation 16416 of database 16417; blocked by process 6973. Process 6973: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') Process 11039: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:55 CEST:yy@xx1:[6973] HINT: See server log for query details. 2014-09-22 22:21:55 CEST:yy@xx1:[6973] STATEMENT: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:55 CEST:yy@xx1:[11039] LOG: process 11039 acquired ExclusiveLock on tuple (4,9) of relation 16416 of database 16417 after 1000.224 ms 2014-09-22 22:21:55 CEST:yy@xx1:[11039] STATEMENT: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:55 CEST:yy@xx1:[11039] LOG: duration: 1004.543 ms execute unnamed: update AELDATA set LOCK_COLUMN = 1 where (SBO_GRP = '15') 2014-09-22 22:21:55 CEST:yy@xx1:[11039] LOG: duration: 0.084 ms parse unnamed: select AEL_ID, SBO_GRP
Re: [GENERAL] hidden junk files in ...data/base/oid/
Hello, thanks for your answer. I've identified problems in my cluster agent script. It is a custom written script with built in automated recovery of failed slave. It was written in time when postgres 9.1 streaming replications feature was just in beta release and there was no postgres agent for streaming replications available out there. The problem was that the failed slave recovery was hardcoded into start operation. But this start operation was aborted by pacemaker due to startup operation timeout. This occured before having finished backup from master to failed slave (in case of bigger database). This is the point where rsync could be aborted and left over temporary junk files. There was no cleanup before re-running the backup from master (using rsync). This may be the reason why there may be left rsync temporary files. Second problem identified is what you write: copying stuff from one direction first, then failed over, then copied in the opposite direction. This was caused because my agent was missing the lock file that standard clusterlabs pgsql agent uses to avoid starting failed master in case of double failure followed by reboot. Now I'm migrating to the standard pacemaker's postgres cluster agent provided by clusterlabs.org to avoid such issues. It is surely much better tested by plenty of installations worldwide with community feedback. In addition I need to automate single (master or slave) failure recovery as much as possible. For this purpose I plan to introduce a new resource on top of pgsql resource which would recover failed pgsql slave(or master) in case master is active on another node (I use only two node cluster). Manual recovery by operator would be needed for cases when postgres on both nodes is down to avoid accidental data loss. Do you know whether there is such cluster agent already available? Best Regards, Andrej 2014-05-27 16:09 GMT+02:00 Alvaro Herrera alvhe...@2ndquadrant.com: Andrej Vanek wrote: Hello, solved. This is not a postgres issue. The system was used in HA-cluster with streaming replications. The hidden files I asked for were created probably by broken (killed) rsync. It uses such file-format for temporary files used during copying. This rsync is used by master to slave database synchronization (full on-line backup of master database to slave node) before starting postgres in hot-standby mode on slave the node... You not only have leftover first-order rsync temp files (.N.uvwxyz) -- but also when those temp files were being copied over by another rsync run, which created temp files for the first-order temp files, leaving you with second-order temp files (..N.uvwxyz.opqrst). Not nice. I wonder if this is anywhere near sanity -- it looks like you're copying stuff from one direction first, then failed over, then copied in the opposite direction. I would have your setup reviewed real closely, to avoid data-corrupting configuration mistakes. I have seen people make subtle mistakes in their configuration, causing their whole HA setups to be completely broken. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
[GENERAL] hidden junk files in ...data/base/oid/
Hello,
I ran upgrade from 9.1 to 9.3 on CentOS using pg_upgrade and database
cluster size dropped from 77GB to 4.3GB. I wonder how this could happen. No
data lost. This means I had about 70GB junk files in my database...
I checked top 10 biggest database tables: nearly same size on old/new
cluster, the biggest table about 2GB, next 1GB, all other tables smaller
and smaller..
I checked files of biggest database (/var/lib/pgsql/9.1/data/base/27610):
# du -sm * |awk '{s+=$1} END {print s}'
7107
# du -sm . |sort -n
75264 .
So size of non-hidden database files is 1/10th of the whole database size.
Question: Where does the 70GB hidden files in ...data/base/oid/ come
from? Which postgres process could generate them? Some missed
maintenance from my side? A bug? Anybody else experienced such issue?
Thanks, Andrej
-details: ...data/base/oid file listing shortened:
# ls -la
total 77069960
drwx--. 2 postgres postgres 32768 May 12 18:50 .
drwxr-xr-x. 5 postgres postgres 4096 May 12 11:47 ..
-rw---. 1 postgres postgres 354156544 Feb 25 12:06 .27623.17rLmT
-rw---. 1 postgres postgres 338952192 Feb 25 14:21 .27623.6dH1b6
-rw---. 1 postgres postgres5767168 Mar 7 16:00 ..27623.6dH1b6.6PrU4B
-rw---. 1 postgres postgres 411041792 Feb 25 15:07 .27623.aN42DG
-rw---. 1 postgres postgres 342884352 Mar 8 15:16 ..27623.aN42DG.0U5xfj
-rw---. 1 postgres postgres 343146496 Mar 8 13:13 ..27623.aN42DG.2WFmNo
-rw---. 1 postgres postgres 343408640 Mar 8 10:43 ..27623.aN42DG.384SXU
-rw---. 1 postgres postgres 357302272 Mar 8 05:26 ..27623.aN42DG.3hHjZ8
-rw---. 1 postgres postgres 360185856 Mar 7 18:19 ..27623.aN42DG.5lWta4
-rw---. 1 postgres postgres 343146496 Mar 8 10:12 ..27623.aN42DG.64lNVQ
...shortened...
-rw---. 1 postgres postgres 1005322240 Feb 25 15:38 .27731.2.JKYXGW
-rw---. 1 postgres postgres 359661568 Mar 9 14:52 ..27731.2.JKYXGW.3h8RuF
-rw---. 1 postgres postgres 331087872 Mar 9 07:37 ..27731.2.JKYXGW.3hK5aF
-rw---. 1 postgres postgres 359923712 Mar 9 09:29 ..27731.2.JKYXGW.3KA5Cq
-rw---. 1 postgres postgres 359923712 Mar 9 16:55 ..27731.2.JKYXGW.45nQei
-rw---. 1 postgres postgres 137363456 Mar 9 04:47 ..27731.2.JKYXGW.4zya2Z
...shortened...
-rw---. 1 postgres postgres 769916928 Feb 25 15:53 .27902.YboxvS
-rw---. 1 postgres postgres 671612928 Feb 20 10:01 .27902.YMEtoS
-rw---. 1 postgres postgres 159645696 Feb 25 16:24 .59866.Lkyxgs
-rw---. 1 postgres postgres 272629760 Feb 20 18:37 .59866.RTcUkC
-rw---. 1 postgres postgres 505151488 Feb 25 16:40 .59961.5BcZpK
-rw---. 1 postgres postgres 91750400 Feb 25 16:55 .60194.gUqSdJ
-rw---. 1 postgres postgres 8192 Apr 7 05:20 60592
-rw---. 1 postgres postgres 8192 Jan 31 13:03 60594
-rw---. 1 postgres postgres 8192 Apr 7 02:01 60596
-rw---. 1 postgres postgres 8192 Feb 28 14:44 60598
-rw---. 1 postgres postgres 8192 Apr 7 11:55 60600
...shortened...
-rw---. 1 postgres postgres 139264 May 12 12:08 702364
-rw---. 1 postgres postgres 24576 May 9 12:42 702364_fsm
-rw---. 1 postgres postgres 8192 May 9 12:40 702364_vm
-rw---. 1 postgres postgres 0 May 9 10:10 702369
-rw---. 1 postgres postgres 860160 May 12 12:08 702372
-rw---. 1 postgres postgres 24576 May 9 12:37 702372_fsm
-rw---. 1 postgres postgres 8192 May 9 12:42 702372_vm
-rw---. 1 postgres postgres 8192 May 9 10:10 702377
-rw---. 1 postgres postgres 499712 May 12 12:08 702381
...shortened...
-rw---. 1 postgres postgres 16384 May 9 14:34 704207
-rw---. 1 postgres postgres 16384 May 9 14:34 704208
-rw---. 1 postgres postgres 8192 May 9 14:34 704209
-rw---. 1 postgres postgres 16384 May 9 14:34 704210
-rw---. 1 postgres postgres 16384 May 9 14:34 704211
-rw---. 1 postgres postgres512 May 9 14:34 pg_filenode.map
-rw---. 1 postgres postgres 106804 May 12 18:50 pg_internal.init
-rw---. 1 postgres postgres 4 Jan 28 13:52 PG_VERSION
Re: [GENERAL] hidden junk files in ...data/base/oid/
Hello, solved. This is not a postgres issue. The system was used in HA-cluster with streaming replications. The hidden files I asked for were created probably by broken (killed) rsync. It uses such file-format for temporary files used during copying. This rsync is used by master to slave database synchronization (full on-line backup of master database to slave node) before starting postgres in hot-standby mode on slave the node... Best Regards, Andrej
Re: [GENERAL] Using Postgresql as application server
On 19 August 2011 04:16, Merlin Moncure mmonc...@gmail.com wrote: It's been around for a long time already: http://asmith.id.au/mod_libpq.html mod_libpq looks like it hasn't been updated in quite a while (apache 1.3 only) -- I think a node.js http server is superior in just about every way for this case. I 100% agree with the comments on the page though. Tad late to chime in, but mod_libpq2.c is available from the good man, too. http://asmith.id.au/source/mod_libpq2.c Compiles installs fine on Slackware64 13.1: sudo /usr/sbin/apxs -i -a -c -I /usr/include/postgresql/ -I /usr/include/httpd -lpq -o mod_libpq.so -n MOD_LIBPQ mod_libpq2.c using postgresql 9.0.4 apache 2.2.19 Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Book
Can anyone recommend PostgreSQL 9.0 High Performance by G. Smith? Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Book
Thanks all - book ordered :} -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regular disk activity of an idle DBMS
This message has been digitally signed by the sender. Re___GENERAL__Regular_disk_activity_of_an_idle_DBMS.eml Description: Binary data Hi-Tech Gears Ltd, Gurgaon, India Sent using PostMaster by QuantumLink Communications Get your free copy of PostMaster at http://www.postmaster.co.in/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regular disk activity of an idle DBMS
Nothing changes there. When OpenFire, Courier-MTA and Apache are restarted, a few numbers change, but othrewise they remain unchanged pretty long. There is no obvious activity that could trigger a disk write 20 times a minute... How many databases are in your pg cluster? There are currently 19 of them, but only about 5 are used actively (queried at least once a day). smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Regular disk activity of an idle DBMS
Hello, after configuring a new home server with PostgreSQL 9.0.4, I observe some regular disk activity, even though the server is completely idle (disconnected from the network, no users but one logged in). There are very short write bursts once in about 3 seconds. There are a couple of things that can cause unexpected disk activity: -autovacuum running in the background. Setting log_autovacuum_min_duration may help you determine when this is happening. -checkpoint activity. Turning on log_checkpoints, as well as looking for changes in the pg_stat_bgwriter view, may help explain if this is the case. I repeatedly looked at that view, but it did not change during at least three *minutes*, so there is probably no unexpected checkpoint activity. -Hint bit updates. Even if you are only reading from a table, in some situations write activity can be generated. See http://wiki.postgresql.org/wiki/Hint_Bits for more information. -Statistics collector updates. If the one logged in user is doing anything at all, they might be generating something here. I identified the most active process, at least twenty times more active than any other process on the system: postgres 3086 0.1 0.0 34688 2584 ?Ss 03:11 1:16 postgres: stats collector process So it's the statistics collector. However, there does not seem to be any database activity at all. I tried looking at the numbers returned by this query: select datname, tup_returned, tup_fetched from pg_stat_database ; Nothing changes there. When OpenFire, Courier-MTA and Apache are restarted, a few numbers change, but othrewise they remain unchanged pretty long. There is no obvious activity that could trigger a disk write 20 times a minute... Andrej smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Regular disk activity of an idle DBMS
Hello, after configuring a new home server with PostgreSQL 9.0.4, I observe some regular disk activity, even though the server is completely idle (disconnected from the network, no users but one logged in). There are very short write bursts once in about 3 seconds. This does not affect performance in any way, but I would like to know whether this is normal. Perhaps I misconfigured something. With the 8.4.x version I used before, there were no such regular disk writes. I used the following approach to detect who is writing to disk: http://www.xaprb.com/blog/2009/08/23/how-to-find-per-process-io-statistics-on-linux/ This is what I obtained after about 3 minutes of observation: TASK PID TOTAL READ WRITE DIRTY DEVICES postgres 10437 10736 0 10736 0 dm-2 md127_raid5630648 0648 0 sdc, sda, sdb, sdd flush-253:3 29302553 0553 0 dm-3 jbd2/dm-2-8 3411 62 0 62 0 dm-2 flush-253:2 3835 35 0 35 0 dm-2 jbd2/dm-3-8 3413 20 0 20 0 dm-3 jbd2/dm-1-8 3409 12 0 12 0 dm-1 flush-253:1465 11 0 11 0 dm-1 postgres 10434 9 0 9 0 dm-2 jbd2/dm-5-8789 6 0 6 0 dm-5 postgres 850 4 0 4 0 dm-2 bash 400 4 0 4 0 dm-5 flush-253:5398 4 0 4 0 dm-5 These are my (non-default) PostgreSQL settings: # grep -Pv '^[ \t]*#|^[ \t]*$' /var/lib/postgres/data/postgresql.conf listen_addresses = '::1,2002:53f0:5de8::1,2002:53f0:5de8:1::1,2002:53f0:5de8:2::1,2002:53f0:5de8:3::1,127.0.0.1,83.240.93.232,10.0.1.1,10.0.2.1,10.0.3.1' max_connections = 128 # (change requires restart) ssl = on# (change requires restart) shared_buffers = 512MB # min 128kB temp_buffers = 64MB # min 800kB max_prepared_transactions = 128 # zero disables the feature work_mem = 16MB # min 64kB maintenance_work_mem = 128MB# min 1MB max_stack_depth = 16MB # min 100kB effective_io_concurrency = 3# 1-1000. 0 disables prefetching checkpoint_segments = 16# in logfile segments, min 1, 16MB each log_destination = 'syslog' # Valid values are combinations of autovacuum_max_workers = 8 # max number of autovacuum subprocesses datestyle = 'iso, dmy' lc_messages = 'cs_CZ.UTF-8' # locale for system error message lc_monetary = 'cs_CZ.UTF-8' # locale for monetary formatting lc_numeric = 'cs_CZ.UTF-8' # locale for number formatting lc_time = 'cs_CZ.UTF-8' # locale for time formatting default_text_search_config = 'pg_catalog.cs' The machine runs ArchLinux. It is a standard piece of x86_64 commodity hardware. There are four SATA drives configured as a RAID5 array. The file system is ext4. Is there an easy way to detect what exactly causes PostgreSQL to write these small amounts of data on an idle machine on a regular basis? Stopping all daemons that connect to PostgreSQL (OpenFire, Apache, Courier-MTA) does not change anything. Any hints would be very helpful. There is actually no performance or usability issue. I just want to *understand* what is going on. Andrej smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Preventing OOM kills
On 25 May 2011 12:32, Yang Zhang yanghates...@gmail.com wrote: PG tends to be picked on by the Linux OOM killer, so lately we've been forcing the OOM killer to kill other processes first with this script: while true; do for i in `pgrep postgres`; do echo -17 /proc/$i/oom_adj done sleep 60 done Is there a Better Way? Thanks in advance. Add more RAM? Look at tunables for other processes on the machine? At the end of the day making the kernel shoot anything out of despair shouldn't be the done thing. Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using column aliasses in the same query
On 18 April 2011 22:06, Tore Halvorsen tore.halvor...@gmail.com wrote: Well, refering to the computed value may be nonsensical, but couldn't it be some sort of query rewrite? So that... SELECT x/y AS z FROM tab WHERE y 0 AND z 2 ... is a shorthand for SELECT x/y AS z FROM tab WHERE y 0 AND x/y 2 No big deal, since there are lots of other ways to do this. That's an accurate observation, but has nothing to do w/ what the original poster was looking for, nor does it refute Toms argument against the OPs suggestion. Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is Postgres 9 supported on Ubuntu Desktop10LTS?
On 25 March 2011 08:42, jcoder leid...@googlemail.com wrote: I've been trying to install postgresql9 on my ubuntu Desktop10 machine and it seems that it is not supported, as the only version that is available in the software center is Pgsql 8.4? Having googled this, i cannot find a straight answer. Is Pgsql9 supported on Ubuntu10LTS? Having googled this (I don't use Ubuntu, and compile postgres from source) I found this after about 1 minute ... https://launchpad.net/~pitti/+archive/postgresql Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql install problem
On 22 March 2011 08:54, Alex wsopsta...@gmail.com wrote: It seems that I don't get the postgresql server installed or available for me to run in admin tools then services. postgresql is not listed as a service available to start or stop! Why do I not get the service installed and running? What OS? Which postgres version? Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] regexp match in plpgsql
On 23 February 2011 11:55, Gauthier, Dave dave.gauth...@intel.com wrote: I would expect to see... ShouldBeOK99 is a match Should_Fail_match77 is not a match Why would you expect that? Both strings match at least one character from the character class? Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] iPad and Postgresql...
On 17 January 2011 13:34, Jerry LeVan jerry.le...@gmail.com wrote: Hi, Hi Jerry, Is there an idiot installable package for MacOsX Snow Leopard that will provide a 'better' user experience for accessing Pg via the web, especially when viewed on the iPad? It's not perl, but have you considered http://phppgadmin.sourceforge.net/ ? Thanks, Jerry Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CSV-bulk import and defaults
On 3 January 2011 11:22, Thomas Schmidt postg...@stephan.homeunix.net wrote: Thus - do you have any clue on designing an fast bulk-import for staging data? As you're talking about STDIN ... have you considered piping the input-data through awk or sed to achieve a pre-populated empty meta data field? Easy enough, low CPU overhead. Thanks in advance, Thomas Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please Help...
Now if you told people the OS, and the version of Postgres maybe ... ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot Start Postgres After System Boot
On 22 October 2010 07:45, Rich Shepard rshep...@appl-ecosys.com wrote: When I run 'ps ax | grep post' I found a few postgres processes. I tried '/etc/rc.d/rc.postgresql stop' but that had no effect. I killed the lowest numbered process and that removed them all. However, I still cannot start a new postgresql process. I just stumbled upon your post from two years ago; has your setup changed since then? Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot Start Postgres After System Boot
On 21 October 2010 11:53, Rich Shepard rshep...@appl-ecosys.com wrote: If someone would be kind enough to point out what I'm doing incorrectly (e.g., removing /tmp/.s.PGSQL.5432 and postmaster.pid when the startup process complains they're not right) I'll save this information for the next time. I can also provide the 'start' section of the Slackware init file so I could learn why it's not working properly. Please do - provide the section, I mean. TIA, Rich Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot Start Postgres After System Boot
On 21 October 2010 16:50, Tom Lane t...@sss.pgh.pa.us wrote: could be reduced to just: else su postgres -c 'postgres -D /var/lib/pgsql/data ' exit 0 fi I'm not sure about your comment that manual start attempts fail with LOG: could not bind IPv4 socket: Address already in use It's pretty hard to believe that that could occur on a freshly booted system unless the TCP port was in fact already in use --- ie, either there *is* a running postmaster, or something else is using port 5432. I concur on both accounts; I would like to see the output of the actual script, though, when it refuses to start; and also a netstat -anp | grep 5432 Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] error migrating database from 8.4 to 8.3
On 9 February 2010 02:55, Marc Lustig m...@marclustig.com wrote: Due to a server issue we needed a reinstallation of Ubuntu along with a downgrade to Ubuntu Hardy. So this is what we did: - copied all from /var/lib/postgresql/8.4/main/ to the new server /var/lib/postgresql/8.3/main/ - edited /var/lib/postgresql/8.3/main/postmaster.opts to correct the path Now trying to start the server results in * Error: The server must be started under the locale : which does not exist any more. I googled and found that people ran into this problem due to different architectures (32 vs 64 bit). In this case, the architecture is definiately the same. In addition to Filip's note: this copy of datafiles ONLY works with a) same architecture and b) same postgres major version that's 8.X.y, where 8.x comprises the major. So 8.4 to 8.3 isn't going to fly. Export/import is the only option. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL]
On 4 February 2010 02:26, vijayalakshmi thiruvengadam tviji1...@hotmail.com wrote: Good Morning, I am a developer trying to use postgresql 8.3.2 when it was available. Now it says fatal error ie when executing postgresql-8.3-int.msi that has two msi files pgadmin3.msi and psqlodbc.msi, it shows May I ask why you're trying to install an ancient version? The latest in the 8.3.x branch is 8.3.9 I would be grateful if you would let me know about it. Many thanks, Vijayalakshmi Vijaya sankar Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] array element replace ?
2010/1/22 Gauthier, Dave dave.gauth...@intel.com: Is there a clever way to replace a single element in an array with another value? E.g. x = array[‘a’,’b’,’c’,’d’]; I want to replace ‘b’ with ‘x’. Not sure you can replace an array value with an array (which kind of is what you're asking in your example), but: http://www.postgresql.org/docs/8.4/static/arrays.html Specifically section 8.14.4. Modifying Arrays Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] data dump help
2010/1/19 Bret S. Lambert bret.lamb...@gmail.com: Isn't this just over-engineering? Why not let the database do the work, and add the column with a default value of 0, so that you don't have to modify whatever 3rd-party app dumps the data: But what if his third-party software does something silly like a select * on the table and then gets a hissy fit because the data doesn't match the expectations any longer? Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] log_temp_files confusion
2010/1/14 Filip Rembiałkowski plk.zu...@gmail.com: I would like to log usage of temporary files for sort/join operations, but only when size of these files exceeds some threshold. So I set in postgresql.conf (this is 8.4.2) log_temp_files = 4MB Just a wild guess... the DOCU says it's an integer, not an INT STRING. Try log_temp_files = 4194304 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ora2pg and DBD::Pg
2009/11/23 Alexandra Roy alexandra@bull.net: Hi all, Hi Alexandra, And what about on the fly please ? As I encounter compilation problem on AIX 5.3, I am wondering if DBD::Pg is necessary to use ora2pg... Josh pointed that out to you 4 days ago. It means w/o having to save intermediate files. Thank you for your help, Regards, Alexandra Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sudoku in an sql statement
2009/11/5 marcin mank marcin.m...@gmail.com: btw2: is SQL with 'with recursive' turing-complete ? Anyone care to try a Brainf*ck interpreter ? :) Sick, sick puppy! :} Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sudoku in an sql statement
2009/11/5 Richard Broersma richard.broer...@gmail.com:
On Wed, Nov 4, 2009 at 3:18 PM, marcin mank marcin.m...@gmail.com wrote:
---
534678912672195348198342567859761423426853791713924856961537284287419635345286179
(1 row)
broersr= with recursive x( s, ind ) as
broersr- ( select sud, position( ' ' in sud )
broersr( from (select '53 76 195986 8 6 34 8 3 17 2
broersr' 6 628419 58 79'::text as sud) xx
Get rid of that line-wrap - it screwed up the spacing.
broersr( union all
broersr( select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
broersr( , position(' ' in repeat('x',ind) || substr( s, ind + 1 ) )
broersr( from x
broersr( , (select gs::text as z from generate_series(1,9) gs)z
broersr( where ind 0
broersr( and not exists ( select null
broersr( from generate_series(1,9) lp
broersr( where z.z = substr( s, ( (ind - 1 ) / 9 )
* 9 + lp, 1 )
broersr( orz.z = substr( s, mod( ind - 1, 9 ) -
8 + lp * 9, 1 )
broersr( orz.z = substr( s, mod( ( ( ind - 1 )
/ 3 ), 3 ) * 3
broersr( + ( ( ind - 1 ) / 27 ) * 27 +
lp
broersr( + ( ( lp - 1 ) / 3 ) * 6
broersr( , 1 )
broersr( )
broersr( )
broersr- select s
broersr- from x
broersr- where ind = 0;
s
---
(0 rows)
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] another can't connect
2009/6/29 BJ Freeman bjf...@free-man.net: ACCEPT tcp -- localhostlocalhost tcp dpt:postgres state NEW What about established connections? tcp0 0 127.0.0.1:5432 0.0.0.0:* LISTEN is the only line for that port I think Chris meant What about iptables rules for established connection? ... Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] prepared statements and DBD::Pg
2009/5/7 JP Fletcher jpfle...@ca.afilias.info: Hi, I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the first command in a prepared statement is 'CREATE TEMP TABLE'. For instance, this works: my $prepare_sql =SQL; CREATE TEMP TABLE foo( id int, user_id int,); INSERT INTO foo(1, 1); INSERT INTO foo(2, 2); SQL my $sth = $dbh-prepare($prepare_sql); This produces the error ERROR: cannot insert multiple commands into a prepared statement Blessed be CPAN and the manuals for DBD http://search.cpan.org/~turnstep/DBD-Pg-2.13.1/Pg.pm#prepare WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements by sending them to the backend to be prepared by the Postgres server. Statements that were legal before may no longer work. See below for details. The prepare method prepares a statement for later execution. PostgreSQL supports prepared statements, which enables DBD::Pg to only send the query once, and simply send the arguments for every subsequent call to execute. DBD::Pg can use these server-side prepared statements, or it can just send the entire query to the server each time. The best way is automatically chosen for each query. This will be sufficient for most users: keep reading for a more detailed explanation and some optional flags. Queries that do not begin with the word SELECT, INSERT, UPDATE, or DELETE are never sent as server-side prepared statements. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql installation with ssh connection.
2009/4/3 dfx d...@dfx.it: dear Sirs, The default installation (#yum install opstresql)suggest the version 8.1 but I would like to install the latest 8.3, so I suppose that I have to change some file containing yum directive per postgresql. 30 seconds on the postgres website ... ran into this ;} http://yum.pgsqlrpms.org/ Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [GENEAL] dynamically changing table
2009/3/31 A B gentosa...@gmail.com: One option is, put it in a db as a huge text (or in textfiles, one per object) and parse it when you need it. That might also work. Why are you demanding sanity? I need crazy ideas to get this to work ;-) Heh ... sorry, but dynamic table just SCREAMS design flaw!! ... as pointed out above, an approach with the new columns being rows in a separate table sounds quite sane. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgsql announce now on twitter
2009/3/5 Joshua D. Drake j...@commandprompt.com: Not sure what the complainer is talking about here. pgsql-announce is moderated so spam should be almost nil. Isn't she the one who keeps complaining about the reply-to-all on the list and isn't subscribed because of the volume? ;} -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slow database creation
2009/2/11 Rob Richardson rob.richard...@rad-con.com: [... snipped ...] In order to test without disturbing the customer's production, I created a copy of their production database on the production server. I often create test databases, and I've never seen the CREATE DATABASE command take longer than five seconds. On the customer's production machine, the command took 167 seconds. [... snipped ...] The customer's machines run Windows Server 2003. My machine runs Windows XP Professional. The application is written in C++. Now I'm not a windows-expert by any stretch of the imagination, but could there be some anti-virus products involved at the customer site? Rob Richardson Product Engineer Software Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about COPY command
2009/1/9 Josh Harrison joshq...@gmail.com: My question is is it possible to read the oracle data from the oracle database and copy them into postgresql database directly by using COPY command instead of jdbc preparedstatement(INSERT command) ? Should be possible; just got to make sure that you have all the formatting right, you may need something like sed or awk to make them match ... Of course it also depends on the complexity of database; if you have several tables w/ referential integrity then copy obviously wouldn't be the right tool for the job. Thanks Josh Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Automatic CRL reload
Hello, this is just a small wish / feature request. The Apache httpd project dealt with a similar issue just a few months ago: https://issues.apache.org/bugzilla/show_bug.cgi?id=14104 There's exactly the same problem in PostgreSQL. The documentation says: The files server.key, server.crt, root.crt, and root.crl are only examined during server start; so you must restart the server for changes in them to take effect. (http://www.postgresql.org/docs/8.3/static/ssl-tcp.html) This is perfectly fine for server.key, server.crt and root.crt. These files change quite rarely. However, root.crl usually chages once a month (which is the default in OpenSSL) or even more often when necessary. Restarting the server once a month is not an ideal solution. With an expired CRL, SSL connections are refused without proper error messages. This is confusing. Despite the fact that root.crl is up-to-date and clients are configured properly, you will get this error message when the old CRL in memory expires: Dec 25 05:37:41 charon postgres[28210]: [4-1] LOG: could not accept SSL connection: no certificate returned It might be better to say something like I can't validate the certificate with an expired CRL. Presumably, pg_ctl restart fixed this problem. It would be great if PostgreSQL could reload the CRL when necessary, just like Apache httpd does. Could this be appended to the 8.4 wishlist, please? Best regards, Andrej Podzimek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
2008/11/18 aravind chandu [EMAIL PROTECTED]: Hello, Hi! I guess most of you guys heard about In Memory Database.I have a small question regarding it.I need to create an In Memory Database for postgresql through which I have to perform various operations on postgresql database(queries,procedures,programs using pqxx API etc...).I didn't have any idea of how to start and where to start this issue.Please comment on this issue,so that it will be really helpful to me . The fact aside that it's a bad idea (tm): you could have the data files reside in a RAM disk. Which OS are you planning to do this on? Thanks, Avin. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
On 04/08/2008, Lew [EMAIL PROTECTED] wrote: Was the message to which you responded posted to the newsgroup? It isn't appearing in my newsreader. Who wrote the message you quoted (you failed to cite the source)? He was quoting Alban Hertroys, and it appeared on the general mailing list (I didn't even know there was a Newsgroup). There seems to be a problem with your mail address, however ... ;} -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)
On 04/08/2008, Scott Marlowe [EMAIL PROTECTED] wrote: One last thing. I'd rather my emails just get dropped silently if that's the minimum someone can do. Use a valid email address that goes to /dev/null and I'll be happy. You may miss a few things sent directly to you, but since that's not what you want anyway, it's no big loss, right? Aye ... dodgy spam-protection methods like that really suck. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
2008/7/28 Rich Shepard [EMAIL PROTECTED]: Thank you. I think that for some reason using pg_ctl to start the postmaster is no longer working here. As I have time, I'll look into why. Can you do a 'locate pg_ctl|xargs ls -l' and see whether you have more than one installed, and if so, which one comes first in the PATH? Rich Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On 27/07/2008, Rich Shepard [EMAIL PROTECTED] wrote: Andrej, Hi Rich, I found the thread in the archives for June of this year. Re-reading the posted results of running initdb I tried a different approach to starting the server. Instead of using pg_ctl I used 'postgres -D /var/lib/pgsql/data ' (while logged in as user postgres, of course.) That cleaned up a bad shutdown (when I had to reboot the system after it hung), fixed the missing socket, and replaced the .pid. So, it's up and running once again. My question is how best to modify the startup script so the postmaster fires up when the system is rebooted. I don't see an option to 'su' to specify the postgres user's password so I can script this. Have you any recommendation? Since Slackware doesn't use the SysV style of inits but default the easiest way for you to achieve an automatic start-up of postgres on reboot would be to add something like if [ -x /etc/rc.d/rc.postgres ]; then /etc/rc.d/rc.postgres start fi to your /etc/rc.d/rc.local Thanks, Rich Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On 27/07/2008, Tom Lane [EMAIL PROTECTED] wrote: Startup scripts invariably run as root, so 'su' isn't going to ask for a password... And it's nothing to worry about because the script he's using is suing to the postgres user anyway ... regards, tom lane Cheerw, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On 27/07/2008, Rich Shepard [EMAIL PROTECTED] wrote: Well, that's the problem, Andrej. I have that script, and it worked fine with postgres-6.x through -8.1, but failed to correctly start the postmaster after the system reboot. I thought we had established that this issue was caused by the current instance pointing at the old installs data directory? I can try twiddling with the script; it calls pg_ctl, and that should work, but apparently something broke last week. That should be quite easy to tweak, really ... my current script (slightly modified from the one in contrib/startup-scripts) is attached... You may need to change the dirs in the script yet a bit. Thanks, Rich Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm rc.postgres Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On 23/07/2008, Rich Shepard [EMAIL PROTECTED] wrote: When I run the Slackware script, '/etc/rc.d/rc.postgresql start' (script attached), I'm shown a process ID and told the daemon is already running. For example: Since there are no official Slackware postgres packages I'd like to ask where that script came from :) and how you installed postges in the first place. Happy to communicate of the list if you prefer that. TIA, Rich Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On 23/07/2008, Rich Shepard [EMAIL PROTECTED] wrote: Andrej, Hi Rich, Unless others consider this topic to be not appropriate for the list, I don't mind a public conversation. I thought that I attached the script to my original message; regardless, here's the attribution: You did - my bad. I usually ignore attachments on mailing-lists, and did so with yours. I upgraded postgres manually, not creating and using a Slackware package. It worked just fine until yesterday's reboot. Now there's an interesting piece of information :) How long ago did you upgrade it? From which version of pg to which version did you upgrade, and how did you go about it? Chances are indeed that the postmasters logfile (/var/log/postgres) may hold crucial information as Tom suggested. Thanks, Rich Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperl installation
On 15/07/2008, JD Wong [EMAIL PROTECTED] wrote: Hey all I'm want to use plperl but directory does not exist and I did not specifically enable it during installation. -Does anybody know how to install it post-install? Which OS is this on? If it's Linux, which distro? And how did you install, from source? Thanks -JD Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help
On 01/07/2008, Jamie Deppeler [EMAIL PROTECTED] wrote: trying to install Postgresql 8.3 and i keep getting these errors libodbc.so is needed libodbcinst.so is needed Hopefully someone can help me I'll hazard a guess and assume you're using some sort of Linux :} ...which distro are you using, how are you installing postgres? Cheers, Tink -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bottom Posting
On 29/05/2008, Bob Pawley [EMAIL PROTECTED] wrote: ... get their point across up front without making me wade through previous posts which I have already read. Good for you :} I can understand the concept of bottom posting No one advocates bottom-posting here. It's all about intersparsed with relevant bits left standing. The concept of most lists should be the free exchange of ideas in the most efficient manner possible. Which is per agreement on the list intersparsed. Which also allows people only just hopping onto the train of thought to get a good understanding of what a thread is about without having to read the lot top to bottom. May not match your individual preference, but then that's not what the list is about, either. Bob Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Open Source CRM - Options?
On 28/05/2008, Kevin Hunter [EMAIL PROTECTED] wrote: And its python :) That's actually a bigger plus than folks may realize because all three communities (Django, Postgres, Python) share the do-it-the-right-way,-not-just-the-quickest/easiest-way mentality. (At least in my experience.) From an overall quality perspective, this is a huge win for all involved. And if you're developing, you will appreciate the chat rooms/mailing lists/community-in-general for all three for just this reason. How does Zope/Plone fit in there as an alternative in your opinion? :) Kevin Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Open Source CRM - Options?
On 28/05/2008, Joshua D. Drake [EMAIL PROTECTED] wrote: How does Zope/Plone fit in there as an alternative in your opinion? :) Do you really want the answer to that? :P Of course! I know a few people who swear by it (and I've never had to use it ...) -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored procedures in C
On 24/04/2008, Emiliano Moscato [EMAIL PROTECTED] wrote: I have to do some stuff writing stored procedures for Postgres in C. I saw the oficial documentation but it was hard for me to find out how to do a simple function, let's call it query() , that receives a string and uses this string to do a query and return the results. Has anyone some examples? You don't mentioned where else you looked - did you come across these? http://linuxgazette.net/139/peterson.html http://www.faqs.org/docs/ppbook/x15284.htm http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html Thanks in advance... Regards, Emiliano Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema design question
On 29/03/2008, Ben [EMAIL PROTECTED] wrote: I'm working on a project which requires me to keep track of objects, each of which can have an arbitrary number of attributes. Although there will be many attributes that an object can have, the data types of those attributes won't be all that varried (int, float, text, boolean, date, etc.). And a somewhat unorthodox suggestion for the list ... would it be worthwhile considering a different storage mechanism all together, like maybe an LDAP directory type of thing? The query language is admittedly very limited. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)
On 01/04/2008, Steve Crawford [EMAIL PROTECTED] wrote:
One advantage of using a consistent prefix is that when you have
forgotten the exact name of a rarely used command and you are using a
shell with readline support, pg_tabtab will bring up a list of
available commands.
For any value of shell IN {bash, tcsh, zsh}.
sh (default on solaris) and ksh won't, and neither will cmd.exe
But I (as a user of bash) see your point.
Cheers,
Steve
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)
On 27/03/2008, Zdeněk Kotala [EMAIL PROTECTED] wrote: 1) What type of names do you prefer? --- a) old notation - createdb, createuser ... a) Never seen any clashes with other tools in terms of names. And the old sys-admin creed: don't fix it if it ain't broken. 2) How often do you use these tools? --- a) every day (e.g. in my cron) b) one per week c) one time d) never e) occasionally 3) What name of initdb do you prefer? -- -- a) initdb b) pg_initdb c) pg_init d) pg_ctl -d dir init (replace initdb with pg_ctl new functionality) e) What is initdb? My start/stop script does it automatically. a) initdb 4) How do you perform VACUUM? - a) vacuumdb - shell command b) VACUUM - SQL command c) autovacuum d) What is vacuum? c, b, a ... in this order. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)
On 28/03/2008, Dawid Kuroczko [EMAIL PROTECTED] wrote: Agree, except I would prefer pg instead of pgc. With pg I am sure that the comand is generic to the extreme, so I don't have to assume what does c stand for. Control? Create? Client? or Command. Also its about 33% shorter. ;-) And it's been taken for about 35 years by a Unix command called page. From its man-page. PG(1)User Commands PG(1) NAME pg - browse pagewise through text files I really find this whole discussion quite silly; it's about someone's personal preference? Don't get me wrong, I'm a lowly user when it comes to pg, and not a member of the hacker-community. But I've been using postgres for a good number of years now (since v7, I think) and have become quite accustomed to the names of the tools in use, have never observed any clashes with other tools, and as a Linux/Unix sys-admin type of person never had a problem with mistaking createuser for useradd or adduser. And I'll stick by the old maxim: if it ain't broken, don't fix it. Regards, Dawid Cheers, Andrej PS: And I feel it feels more natural to say pg createuser than pgc create user, but that's solely my typing impression. And it's noticeably shorter to just type createuser ;} ... which worked a treat for many many moons. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help to migrate pqSQL db 8.0.3 to 8.2.6
On 26/03/2008, Tri Quach [EMAIL PROTECTED] wrote: Hi Andrej, Hi Tri! I am running on Linux, Red Hat 3. I have hard time to use pg_dump command. Can you give me the syntax of pgdump? Not a syntax-problem; to use pg_dumpall you need to be the postgres (superuser). su - postgres pg_dumpall dbfile If you'd rather run it individually for each DB become the user who owns the respective DB. Thank you for your help. Tri. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use database?
On 25/03/2008, Anton Andreev [EMAIL PROTECTED] wrote: Hi, Hi, How to use a database I have just created in a script that I am executing in Pgadmin3 on Windows? I can not use USE Northwind; or \connect Northwind;? Hard to say w/o knowing the script. Does it just create tables, or does it supposedly create a database as well? Cheers, Anton Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table size and storage location
On 25/03/2008, chuckee [EMAIL PROTECTED] wrote: Thanks but I still get the error 'ERROR: relation capture does not exist' when trying these two alternative functions you mention above. There is definitely a table called 'capture' in my database! Are you sure you're connected to the right database when running that? -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help to migrate pqSQL db 8.0.3 to 8.2.6
On 25/03/2008, Tri Quach [EMAIL PROTECTED] wrote: Hi All, I installed pqsql 8.2.6 on a new server. I need to migrate the data from pqSQL db 8.0.3 on the old server to 8.2.6 on the new server. Can anyone provide me a document how to migrate? It's part of the package. Read the INSTALL document that comes with postgres, it has an UPGRADE section. In a nutshell you want to use pg_dump (or pg_dumpall, depending on how many databases you have loaded) ... you can either save the files, install the new version and import them, or you can (if you start the newer version on a different port than 5432 and have it use a different base directory) have both versions running at the same time. What OS version are you running PG on? And if you can, use 8.2.7 Thank you for your help. Tri Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [postgis-users] how many min. floating-points?
On 22/03/2008, John Smith [EMAIL PROTECTED] wrote: please don't cross-post my cross-post. if i wanted to post it to the postgresql list, i would have ;) That seems to be quite a silly request, considering you were asking for assistance on public lists. no seriously! if i wanted to post it to the postgresql list, i would have. thanks but no thanks. jzs You did. I can't see what would make you think you hadn't. Here's the relevant header part from you original message: 8888SNIP8888 Message-ID: [EMAIL PROTECTED] Date: Thu, 20 Mar 2008 14:02:12 -0400 From: John Smith [EMAIL PROTECTED] To: PostgreSQL General pgsql-general@postgresql.org Subject: [GENERAL] [postgis-users] how many min. floating-points? Cc: PostGIS Users Discussion [EMAIL PROTECTED] 8888SNIP8888 Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [postgis-users] how many min. floating-points?
On 21/03/2008, Colin Wetherbee [EMAIL PROTECTED] wrote: Dunno about that. On the PostGIS list, he said: i got an old box supporting only 1 floating-point Maybe he means an FPU? *boggle* Maybe floating-point registers on the FPU? So many options! Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dump format for long term archiving.
On 14/03/2008, brian [EMAIL PROTECTED] wrote: The version you dump it from is unlikely to be difficult to find ten years from now. I'd just make sure to append the pg version to the archive so it's obvious to any future data archaeologists what's needed to breathe life back into it. Let me play devils advocate here ... While the source for PG 8.x will be around there's no guarantee that future enhancements to gcc (or whatever commercial compiler you'll be using) will still allow you to compile it w/o potentially long- winded modifications to the original source. My gut-feeling is that trying to keep data as a moving target, with some redundancy in terms of storage and hardware, and updating the appropriate means every few years (financial life-cycle?) is a sensible method :} Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgre vs MySQL
On 14/03/2008, rrahul [EMAIL PROTECTED] wrote: Thanks to all you wonderful people out their. I don't know if its your love for Postgres or nepothism that makes it look far superior than mysql. But why does the client list dosen't tell that? I see Mysql bosting for Google,Yahoo, Alcatel.. What about Postgres the list is not that impressive. What can I say? 96% of personal computers run some form of windows. Does that mean it's a superior product to a PC running Linux, or a Mac w/ MacOS? I'd say no (actually more like NOOO!), because windows doesn't let me do 80% of the things that I do (need to do) with my PC. From the fact hat the user-base is so massive, can I deduce that windows is superior in terms of security or easy maintenance? My personal experience says No, no way. What then? Could it be marketing or the sad results of a avalanche effect? Geee, there's a thought. cheers, Rahul. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgre vs MySQL
On 14/03/2008, Steve Crawford [EMAIL PROTECTED] wrote: What can I say? 96% of personal computers run some form of windows. Does that mean it's a superior product to a PC running Linux, or a Mac w/ MacOS? I'd say no (actually more like NOOO!), because windows doesn't let me do 80% of the things that I do (need to do) with my PC. From the fact hat the user-base is so massive, can I deduce that windows is superior in terms of security or easy maintenance? My personal experience says No, no way. Whoa! Sure glad I read this thread. I was about to buy a Bentley but now that I know that sales figures are the sole measure of quality I can see that the Kia is clearly a superior vehicle. Heh. :} A silly question in this context: If we know of a company that does use PostgreSQL but doesn't list it anywhere ... can we tahttp://www.securecomputing.com/techpubsRC.cfm?pid=85ke the liberty to publicise this somewhere anyway? E.g. the control center ( http://www.securecomputing.com/techpubsRC.cfm?pid=85 ) uses postgres, the only official attribution (I've seen the binaries in the file-system) is that their product uses port 5432 in the manual. Cheers, Andrej P.S.: This is all really starting to belong to advocacy :} -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] split pg_dumpall backups per database
On 11/03/2008, Luca Ferrari [EMAIL PROTECTED] wrote:
Hi all,
is it possible to instrument pg_dumpall to produce separate sql files for
each
database it is going to backup? I'd like to keep separate backups of my
databases, but using pg_dump can lead to forgetting a database.
You could use the method described here for a single database:
http://archives.postgresql.org/pgsql-general/2008-02/msg00343.php
Just slap a wrapper around it (untested), out.sql being the name of
the dump-file:
for i in $(awk '/^CREATE DATABASE/ {print $3}' out.sql); do dump.sh
${i} out.sql ${i}.sql; done
Of course you won't be getting the creation of specific roles or
anything that way.
Thanks,
Luca
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger to run @ connection time?
On 12/03/2008, Kynn Jones [EMAIL PROTECTED] wrote: If one can set up this insert operation so that it happens automatically whenever a new connection is made, I'd like to learn how it's done. But if not, then I don't see how performing the insert manually every time one connects would be any easier than simply executing the perl_setup() procedure directly. After having thought about this for a few seconds BEG you could conceivably use some OS/DB integration to achieve this. Just make sure postmaster writes new connections to a log, monitor that log from a script, and if it sees a connect have that insert a value into special table of yours that then can do the trigger you looked for? Of course I may not have quite understood how that this procedure adds useful definitions, mostly subs, to Perl's main package. This needs to be done for each connection is meant to work. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PHPs PDO, apache and never ending sessions
On 07/03/2008, Joshua D. Drake [EMAIL PROTECTED] wrote:
A bit of poking around with ps and lsof showed me that a PHP
application I closed days ago (no browser open) was still active
tying up backend sessions; the problem went away when I
restarted my apache. Is this normal behaviour? How do I
deal with it under normal circumstance, am I just supposed
to increase the number of allowed connections and not worry
about apache holding sessions open even after the client
has long gone?
It depends on how you are connecting. For example if you are doing this:
PDO::ATTR_PERSISTENT = true
Then... yeah :). You really shouldn't use a language layer for
persistent connections though. Use pgbouncer or pgpool.
Thanks Joshua. It was indeed set to true; I was playing with
the hatshop database and application from the Beginning
PHP and PostgreSQL E-Commerce Apress book. And found
that config.php has define('DB_PERSISTENCY', 'true');. Thanks
for pointing me in the right direction.
Joshua D. Drake
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PHPs PDO, apache and never ending sessions
Hi, Not sure whether this is the right place to ask (probably isn't) but I've seen much mention of PHP and some of PDO on this list ... I'm currently playing with the above, today I got a message FATAL: connection limit exceeded for non-superusers even though there were no sessions I was aware of open. A bit of poking around with ps and lsof showed me that a PHP application I closed days ago (no browser open) was still active tying up backend sessions; the problem went away when I restarted my apache. Is this normal behaviour? How do I deal with it under normal circumstance, am I just supposed to increase the number of allowed connections and not worry about apache holding sessions open even after the client has long gone? Versions of products in question: apache2 2.2.8 (Unix) PHP 5.2.5 postgres 8.3 Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Requiring a password
On 06/03/2008, Ralph Smith [EMAIL PROTECTED] wrote: I guess I'm missing something. Question part A) Even after reloading and restarting the DB, which shouldn't be necessary, smithrn still gets in w/o any password checks. How come? Impossible to answer w/o knowing any entries in your pg_hba.conf Question part B) I have SSL on, and all the client boxes also have it. If I change HBA, how will / would I change my login? As it is now, a particular user connects to a particular DB w/o any password prompt. e.g.: psql -U username dbname To the best of my knowledge the authentication shouldn't be affected by the transport ... Thank you! Ralph Smith Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
On 27/02/2008, Olmec Sinclair [EMAIL PROTECTED] wrote:
Hi Olmec,
I have encountered a similar situation that came about when I
upgraded to postgres 8.3. Now it won't start stating that:
The server must be started under the locale : which does not
exist any more
(it looks like the locale is set to {blank} ???)
Not sure what I should do here. Removing mostprest to an older version
might be the way to go?
That was Richards suggestion for Geoff who had the
problem. What OS are you running PostgreSQL on?
Olmec Sinclair
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
On 28/02/2008, Olmec Sinclair [EMAIL PROTECTED] wrote: Hello again! And please remember to hit reply-to-all so everyone on the list sees what you're saying. Also please note that top-posting is frowned upon here. :) I am running postgres on Ubuntu 7.10 (recent convert from windows) and I think my previous version of postgresql was 8.2 Last night I un installed 8.3 and reinstalled 8.2 but now I can't get that to start so I'm not making much progress. The problem is I don't really know what I am doing :) By which means did you do the installation/un-install of both versions? What data-directory is/was which version using? I am learning slowly though. Olmec Cheers from sunny Wellington, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
On 28/02/2008, Olmec Sinclair [EMAIL PROTECTED] wrote: Anyway, I uninstalled and installed using the ubuntu package manager. I tried again this morning - removing 8.2 and then reinstalling 8.2 again... still won't start. Are you seeing any error messages? W/o more detail it's impossible to even start trying to support you. What does /var/log/postgresql/postgresql-8.2-main.log say? I'm sure I can work through this (I seem to remember having a starting issue before) but the data locale thing is a worry. That, too, is indeed. Now ... gutsy doesn't have 8.3 officially yet (I don't think). What other repositories did you tie in with your apt? Cheers (from Christchurch) Olmec Cheers, Andrej ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Understanding ps -ef command column
On 23/02/2008, David Jaquay [EMAIL PROTECTED] wrote: When I do a ps -ef, in the command column, I see: postgres: postgres dbname 10.170.1.60(57413) idle This doesn't resemble any ps -ef output I've ever seen. What OS is this on, what's the version of ps? Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Are indexes blown?
On 17/02/2008, Phoenix Kiula [EMAIL PROTECTED] wrote: But this is kind of sitting there, hogging the command prompt. Is there any way I can let it go on in the background? Ouch ... no, that's entirely my fault, wasn't quite awake I guess, and hadn't thought it through completely ... that's not going to give us the desired result... Try this: for z in `seq 1 3600`; do top -b -d 1 -n 1| awk -f top.awk; done | tee topoutput Not sure whether it's going to give us the desire granularity of time... Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(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: [GENERAL] Where is the system-wide psqlrc on RHEL4?
On 10/02/2008, Dean Gibson (DB Administrator) [EMAIL PROTECTED] wrote: It's not installed in the base/server/libs RPMs. I had to search the uninstalled PostgreSQL RPMs for it, and then (temporarily) install the devel RPM to run it. For CentOS 4.4 RHEL4, the system-wide psqlrc is in /etc/sysconfig/pgsql/ Another alternative (quick dirty) would have been to strace -o whereisit psql and grep psqlrc whereisit -- Dean Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ERROR: COPY quote must be a single ASCII character
The answer is simple no. I know I can to some preprocessing on input files using awk, sed etc. but don't want to change the structure of the file. Colin Wetherbee wrote: Andrej Kastrin wrote: ||5354235||,||some text...|| ||1234567||,||some text...|| ||1234568||,||some text...|| The sql statement I defined was: COPY testtable FROM 'test.txt' WITH DELIMITER AS ',' CSV QUOTE AS '||'; but the error: ERROR: COPY quote must be a single ASCII character I guess I'm stating the obvious here, but can you just change your '||' quotes to a single character within the file? Colin ---(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
[GENERAL] ERROR: COPY quote must be a single ASCII character
Dear all, I have to copy the file with the following delimiters into the database: ||5354235||,||some text...|| ||1234567||,||some text...|| ||1234568||,||some text...|| The sql statement I defined was: COPY testtable FROM 'test.txt' WITH DELIMITER AS ',' CSV QUOTE AS '||'; but the error: ERROR: COPY quote must be a single ASCII character Any solution? Thank you in advance. Best, Andrej ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Reload only specific databases from pg_dumpall
On 05/02/2008, Scott Marlowe [EMAIL PROTECTED] wrote:
And a more generic version :}
88888888
#!/bin/bash
# split.sh: a shell script and wrapper for some (g)awk to extract a single
#database out of a dumpall file - a quick and ugly hack, as
#usual no other warranty as that it worked with my test-case. :}
#
# It relies on GNU awk 3, may work with nawk/mawk, but I didn't test that.
# The old traditional awk as shipped with Solaris as the default will most
# definitely barf on this one.
#
# The reason for the writing out of the awk-script to /tmp is the invocation;
# I couldn't figure out a way to pass the filename to the BEGIN part other
# than via a variable, and I couldn't stand the thought of having to enter
# it manually on the command line twice. :} And I didn't like the idea of
# writing/saving two separate scripts - shoot me :D
#
# It creates two temporary files that it hopefully wipes after a
# successful run.
# hacked up by andrej
function usage {
echo Usage: $0 databasename inputfile outputfile
echo
echowhere database is the name of the database you want to isolate
echoout of the dump-file, inputfile is the file generated by
pg_dumpall
echofrom which you want to extract a single database, and
outputfile is
echothe target file you want to write the extracted data to
echo
}
if [ $# -ne 3 ]; then
usage
exit 1
fi
database=$1
input=$2
output=$3
pid=$$
temp=/tmp/awk.prog.$pid
cat $temp \END
BEGIN{
system( fgrep -in \\\connect \ file /tmp/outPut )
while( getline line /tmp/outPut 0 ){
count++
numbers[count]=line
}
for (i=1; i=count;i++ ){
if ( numbers[i] ~ db ){
start = gensub(/([0-9]+):.+/, \\1, g, numbers[i])
stop = gensub(/([0-9]+):.+/, \\1, g, numbers[i+1]) - 1
}
}
matchdb=CREATE DATABASE db.+;
}
{
if( $0 ~ matchdb ){
print
}
if(( NR = int(start) ) ( NR = int( stop ) ) ){
print
}
}
END
sed -i s/outPut/outPut.$pid/ /tmp/awk.prog.$pid
awk -v file=$input -v db=$database -f /tmp/awk.prog.$pid $input $output
rm /tmp/awk.prog.$pid /tmp/outPut.$pid
88888888
Cheers,
Andrej
---(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: [GENERAL] pg_ctl: cannot be run as root
On 05/02/2008, Stefan Schwarzer [EMAIL PROTECTED] wrote: But strange enough, I only can start the postgres server from time to time, normally when I have freshly restarted the machine. Now for example, I did some changes to the postgresql.conf file, and when trying to restart the postgres server, I get this: ... PS: Running Leopard on MacPro. I don't know MacOS very well, and it's been a while that I last had my hands on MacOS X (2 years). Are you saying that you use MacOS as user root by default, rather than as a non privileged account? Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres with daemontools or similar
On 05/02/2008, Chuck D. [EMAIL PROTECTED] wrote: would never know if there was an issue. I was also looking into monit. I don't know what others recommend, but I'd be violently opposed to the automatic re-starting for the very reason you mention above. Monitoring it via monit, nagios or which other tools you have at hand sounds very sensible to me, and I'd go with that for sure. What is the best way to monitor the postmaster and notify or restart on problems? What solutions have other SysAdmin's incorporated successfully? Nagios here, with e-Mail and pager alerts. Monitoring the postmasters presence and the ability to connect to 5432 Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Reload only specific databases from pg_dumpall
Or, for the heck of it: sed -n -e '/DATABASE smarlowe/p' -e '141,334p' test.sql clean.sql ;} Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
On 01/02/2008, Tony Caduto [EMAIL PROTECTED] wrote: The part about the BSD license is bogus. A BSD license is the most desirable of any Open Source license and gives you the right to use PostgreSQL in your commercial apps without worry. While I'm a big fan of the BSD license (for varied reasons) I think that OpenSource hardliners like RMS would argue that the BSD license is *NOT* in the true spirit of OpenSource *BECAUSE* of what you list as a bonus of it ... the locking down of benefits reaped from OpenSource not getting back into the stream. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Members-choice award at LinuxQuestions.org
Hi Guys, Apologies for the cross-post, I already posted this in advocacy a few days ago, and am hoping for a slightly better exposure here. Over at http://www.linuxquestions.org a members-choice award for 2007 is currently going on. Every year I see a great influx of people signing up solely for the purpose of voting for firebird ... to counter- balance that I thought we could maybe get some people to vote for the best OpenSource RDBMS instead? :} Not that firebird wins (most of the time MySQL does :/), but I'd sure like to see more votes for Postgres in that poll. http://www.linuxquestions.org/questions/2007-linuxquestions.org-members-choice-awards-79/database-of-the-year-610185/ Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to automate password requests?
On 11/01/2008, Marten Lehmann [EMAIL PROTECTED] wrote: is there no way to specify the password directly? I don't like to create a separate file because all config is done in a shell script. I could set a certain environment variable with the password, but does pgadmin read a password from such a variable? If yes, what is its name? This is generally a bad idea, because on many systems one can see the environment variables with which a process was started in ps' output. It shouldn't be too hard for your config-script to echo the proper parameters into ~/.pgpass . You best forget about the thought of having a shell variable with the password ;} Regards Marten Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Hijack!
On 12/15/07, Richard Huxton [EMAIL PROTECTED] wrote: L Leif B. Kristensen wrote: O I me too. L t ' On Wednesday 12. December 2007, Gregory Stark wrote: s Alvaro Herrera [EMAIL PROTECTED] writes: Thomas Kellerer wrote: n Joshua D. Drake, 11.12.2007 17:43: o O.k. this might be a bit snooty but frankly it is almost 2008. If t you are still a top poster, you obviously don't care about the people's content that you are replying to, to have enough wits to t not top post. h I personally find non-trimmed bottom postings at lot more annoying e than top-postings. But then that's probably just me. It's not just you. Much as I am annoyed by top-posting, I am much w more so by people who top-post at the bottom. Hey, did I say o something stupid? No -- think about it. These guys do exactly the r same thing as top-posters, except it is much worse because the s actual text they wrote is harder to find. t -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Sharing static data among several databases
On Nov 19, 2007 11:39 AM, Tom Lane [EMAIL PROTECTED] wrote: [ shrug... ] If your lawyers insist on that, wouldn't they also object to all customers linking to the same copy of the shared data? They should, if they know what they're about. You're implying that that lawyers understand what database, schema and shared data are ... ? regards, tom lane Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Sharing static data among several databases
On Nov 19, 2007 12:29 PM, Robert James [EMAIL PROTECTED] wrote: Comedy aside, this makes a lot of sense: The shared data has nothing private in it at all - it's chemical info. Sharing it is no worse than sharing the application code, or the OS's libraries. It's the customer's data which needs to be isolated. I appreciate that. But realistically if you had locked information isolation down via permissions and appropriate views the information for each customer would be as safe as it would using separate databases or even servers. Cheers, Andrej P.S.: I assume this was meant to go to the list, not to me as an individual; try reply-all for this list. -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
