Re: [ADMIN] Help With Database Backup
Hi, Hello there, I am new to PostgreSQL, succeded in running a little database on my dev. machine and now I am having problems exporting the local database to the one I want to use on my site online. I am getting errors when I try to run the SQL online with phpPgAdmin. What errors are you getting? It's impossible to give you any help unless you can provide a bit more information. I exported / backup the local database using pgAmin, I choose the PLAIN format and then selected the No Owner option as well as the Schema Only option (how canI export schema and data at once). I believe if you don't select schema only you get schema and data. Regards, Andy -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Configure pg_hba.conf
Hi Alexander, Use 0.0.0.0/0. You can of course add multiple lines to match all your ranges (e.g. 192.168.0.0/24, 10.0.0.0/8 etc.) Regards, Andy. Alexander B. wrote: Hi people, I would like to configure pg_hba for any IP. I have several network mask, some times 192..., or 10, or 190 ..., ... ... How can I configure for any IP? Thanks Alexander ---(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 !DSPAM:37,46c33ff9107509519219121! ---(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: [ADMIN] [INTERFACES] where postgres 8.1.8 log messages go
Firstly, NEVER point log_directory to your cluster data directory - always keep it separate. (Personally I put all log files under /var/log/application_name.) Secondly, I've copied this to pgsql-admin, not pgsql-interfaces as it's more of an admin issue than programmability. You need to make sure that the PostgreSQL user can write to log_directory. (Ideally log_directory should be owned by postgres, which works well if like me you use /var/log/app name as a different application won't be able to write to postgres's log folder.) Also note, you need redirect_stderr set to on to capture the output into log files, otherwise it'll all go on the console. And while debugging, don't turn on silent mode! Wait till you've got it working then switch silent mode on. Please post the entire logging section of your postgresql.conf if you have further difficulties. Now it's on the right list, more people can chip in and help - I'd advise joining pgsql-admin if you haven't already. Andy. jing han wrote: Hi Andy, Thank you so much for your help. I think what you said are perfectly right. But I still encountered strange problem. I added log_directory='/var/nm2' log_filename='pgsql.log' /var/nm2 is the directory for database files in my system, the directory ownership is postgres:postgres, so I think no permission problem if later postgres server log message into the file under this directory) And for better debugging, I use log_min_error_statement = info After start postmaster, I looked at directory '/var/nm2', didn't find pgsql.log file, then I did some database update action, I still couldn't see pgsql.log generated. I also tried log_directory=/var/log/pgsql the same thing happen. what else I need to do to make things right? jing - Original Message From: Andy Shellam (Mailing Lists) [EMAIL PROTECTED] To: jing han [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 27, 2007 10:57:09 AM Subject: Re: [INTERFACES] where postgres 8.1.8 log messages go Don't forget to copy back to the list - Reply To All is your friend ;) This is my configuration: log_directory = '/var/log/endeavour/pgsql' # Directory where log files are written Note the single quotes, not double - this could be your problem. Also note that /var/log/messages is a file, not a directory. PostgreSQL will write it's own log files.. If you want to use a specific log file, use log_filename in conjunction with log_directory - but note that syslog uses /var/log/messages so don't use that or it'll get syslog confused. You can log to syslog as well, if that's what you want to do. Read that manual page some more. I would also not put the log files in /var/log - at least make a subdirectory like /var/log/pgsql. I've known built-in Linux rotate and compress scripts to play havoc with PostgreSQL by rotating and compressing it's logs while it's trying to use them, when they're in /var/log. Andy. jing han wrote: Hi Andy, Thanks for the information. I tried to add log_destination = /var/log/messages into postgresql..conf, then I started database server, I found that db server throwed the following error message: FATAL: syntax error in file /var/nm2/postgresql.conf line 130, near token Can you tell me the syntax for this? The document said that This option can be set at server start or in the postgresql.conf configuration file., If set at server start, what is the option for this pramater? (for example: -D is for the database file directory) Thank you for your help. jing Original Message From: Andy Shellam (Mailing Lists) [EMAIL PROTECTED] To: jing han [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 27, 2007 9:32:56 AM Subject: Re: [INTERFACES] where postgres 8.1.8 log messages go Wherever you want them to. In your postgresql.conf file, review the logging section. The following will help you: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html Andy. jing han wrote: Hello, I have a question about postgres log messages. postgres 7.2.3 log messages go to /var/log/messages file, postgres 8.1.8 log messages do not go to /var/log/messages, so where they go? Any feedback will be appreciated.. jing Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Any questions? Get answers on any topic at www.Answers.yahoo.com. Try it now.
Re: [ADMIN] host name?
Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place?
Re: [ADMIN] PostgreSQL and VB.net
Vishal Mailinglist wrote: Hi , I have a program already working on LAMP architecture. Now since the business logic is in PostgreSQL. We were interested to build the front end on VB.net. Would like to know the readiness of PostgreSQL working with VB.net . Would appreciate If anyone can suggest any drivers, website and essential reading before I go for coding. -- Regards, Vishal Kashyap. http://vishal.net.in http://vishal.net.in/ !DSPAM:37,45df1256925171694816573! Npgsql is now sufficiently stable for .NET 1.1 and 2.0 (I've personally used it with VB.NET and C#.NET.) I love it, it's a great interface and will be powering a couple of major projects I've got coming up over the next couple of years. Of course there are ODBC drivers too, but I'd advise to go with the a native .NET class - and Npgsql should do you nicely! The developers of Npgsql hang out on [EMAIL PROTECTED] so could be worth dropping a question on there. http://pgfoundry.org/projects/npgsql Andy.
Re: [ADMIN] [pgadmin-support] UNSUBSCRIBE
To all who want unsubscribing, it is best to fill out this form: http://www.postgresql.org/community/lists/subscribe Regards, Andy. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] log_duration?
Ray Stell wrote: I toggle log_duration: wiki=# \!date Mon Feb 19 11:14:35 EST 2007 wiki=# set log_duration=off; SET wiki=# SELECT current_setting('log_duration'); current_setting - off (1 row) yet duration continues to be logged: wiki,13190,wiki,2007-02-19 11:16:00.926 EST,45d94f9e.3386,36108,2007-02-19 02:19:58 EST,270828,BIND LOG: duration: 0.034 ms wiki,13190,wiki,2007-02-19 11:16:00.929 EST,45d94f9e.3386,36109,2007-02-19 02:19:58 EST,0,COMMIT LOG: duration: 2.840 ms do I have to bounce to get it set? Thx. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings !DSPAM:37,45d9d01c18514452914812! Probably best to restart Postgres, yes. I've observed logging functions to not take effect without a restart. Andy.
Re: [ADMIN] 8.2 Admin Pack broken?
Peter Eisentraut wrote: Andy Shellam (Mailing Lists) wrote: Not particularly!! Don't know what it is but it doesn't _appear_ to be GNU. According to man make it is a FreeBSD General Command. I can tell you that ./configure make make install works and builds a working Postgres server just fine, it's just the contribs it doesn't build. There is extra code in the top-level makefile that detects if you don't run GNU make, tries to find it, and calls it instead. But you need to have it installed somewhere. That code, however, is not replicated in each directory of the source tree. In the past, we have usually refered to the GNU make program as gmake, but I see an increasing number of systems, including my own, that no longer installs GNU make as gmake but only as make. So basically you just need to be careful what you call. Ah that provides an explanation, thanks Peter! I installed GNU make before trying to build Postgres as there was another program I needed it for, so I never crossed this problem while building Postgres. Yes, regarding make, I've noticed that all GNU stuff I've compiled on FreeBSD gets installed into /usr/local/ by default, which keeps it separate from the system programs in /usr. HTH Andy
[ADMIN] 8.2 Admin Pack broken?
I'm trying to compile in the adminpack into PostgreSQL 8.2.3, but it doesn't want to build. According to the instructions, I change into contrib/adminpack and run make followed by make install. However, make fails: [EMAIL PROTECTED] /endeavour/software/cache/postgresql-8.2.3/contrib/adminpack]$ make Makefile, line 8: Need an operator Makefile, line 10: Could not find Makefile, line 11: Need an operator ../../src/Makefile.global, line 38: Missing dependency operator ../../src/Makefile.global, line 41: Need an operator --8-- lots more of the above line--8-- ../../src/Makefile.global, line 109: Missing dependency operator ../../src/Makefile.global, line 110: Need an operator --8-- lots more of the above 2 lines--8-- ../../src/Makefile.port, line 3: Need an operator ../../src/Makefile.port, line 12: Missing dependency operator --8-- more of the above 2 lines--8-- ../../src/Makefile.global, line 324: Missing dependency operator ../../src/Makefile.global, line 326: Need an operator --8-- lots more of the above 2 lines--8-- /endeavour/software/cache/postgresql-8.2.3/src/../src/nls-global.mk, line 25: Could not find /endeavour/software/cache/postgresql-8.2.3/src/../contrib/adminpack/nls.mk /endeavour/software/cache/postgresql-8.2.3/src/../src/nls-global.mk, line 29: Need an operator --8-- 4 more of the above line--8-- Error expanding embedded variable. [EMAIL PROTECTED] /endeavour/software/cache/postgresql-8.2.3/contrib/adminpack]$ This is on FreeBSD 6.1, I've built and installed PostgreSQL 8.2.3 (running nicely) - I have Bison and M4 installed - is there anything else I need? The nls-global.mk and nls.mk files don't exist. Any help would be appreciated! Thanks, Andy. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] 8.2 Admin Pack broken?
Never mind, I found the problem. FYI: Reports on Google are wrong - they say that you need to use gmake to build the PostgreSQL server source. You don't. I had installed GNU make on FreeBSD, but it installs it under /usr/local/bin/make - of which the Unix version of make exists under /usr/bin/make. Just typing make builds PostgreSQL just fine with the Unix make. However, the contrib modules *won't* build with the Unix make - I had to run /usr/local/bin/make instead of just make. HTH someone, Andy. Andy Shellam (Mailing Lists) wrote: I'm trying to compile in the adminpack into PostgreSQL 8.2.3, but it doesn't want to build. According to the instructions, I change into contrib/adminpack and run make followed by make install. However, make fails: [EMAIL PROTECTED] /endeavour/software/cache/postgresql-8.2.3/contrib/adminpack]$ make Makefile, line 8: Need an operator Makefile, line 10: Could not find Makefile, line 11: Need an operator ../../src/Makefile.global, line 38: Missing dependency operator ../../src/Makefile.global, line 41: Need an operator --8-- lots more of the above line--8-- ../../src/Makefile.global, line 109: Missing dependency operator ../../src/Makefile.global, line 110: Need an operator --8-- lots more of the above 2 lines--8-- ../../src/Makefile.port, line 3: Need an operator ../../src/Makefile.port, line 12: Missing dependency operator --8-- more of the above 2 lines--8-- ../../src/Makefile.global, line 324: Missing dependency operator ../../src/Makefile.global, line 326: Need an operator --8-- lots more of the above 2 lines--8-- /endeavour/software/cache/postgresql-8.2.3/src/../src/nls-global.mk, line 25: Could not find /endeavour/software/cache/postgresql-8.2.3/src/../contrib/adminpack/nls.mk /endeavour/software/cache/postgresql-8.2.3/src/../src/nls-global.mk, line 29: Need an operator --8-- 4 more of the above line--8-- Error expanding embedded variable. [EMAIL PROTECTED] /endeavour/software/cache/postgresql-8.2.3/contrib/adminpack]$ This is on FreeBSD 6.1, I've built and installed PostgreSQL 8.2.3 (running nicely) - I have Bison and M4 installed - is there anything else I need? The nls-global.mk and nls.mk files don't exist. Any help would be appreciated! Thanks, Andy. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate !DSPAM:37,45d70d9518515637015809! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] 8.2 Admin Pack broken?
Tom Lane wrote: Andy Shellam (Mailing Lists) [EMAIL PROTECTED] writes: FYI: Reports on Google are wrong - they say that you need to use gmake to build the PostgreSQL server source. You don't. I can assure you that you need GNU make to build any part of Postgres. Perhaps what you're dealing with there is that /usr/bin/make is some particularly ancient release of GNU make that has most but not quite all of the features we depend on? make -v might prove informative. Not particularly!! Don't know what it is but it doesn't _appear_ to be GNU. According to man make it is a FreeBSD General Command. I can tell you that ./configure make make install works and builds a working Postgres server just fine, it's just the contribs it doesn't build. [EMAIL PROTECTED] ~]# make -v make: no target to make. [EMAIL PROTECTED] ~]# /usr/bin/make -v make: no target to make. [EMAIL PROTECTED] ~]# /usr/local/bin/make -v GNU Make 3.81 Copyright (C) 2006 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. This program built for i386-unknown-freebsd6.1 [EMAIL PROTECTED] ~]# Confusion reigns.
Re: [ADMIN] WAL files backup
Chad Wagner wrote: On 2/15/07, *Eduardo J. Ortega* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: After erasing the less than names WAL files, we add to tar the remaining WAL records (0003B, 0003C and so on on the example). The more WAL files you have after 0003B, the more up to date DB you get after restore (since it has more WAL files indicating more transactions that took place after the backup. Why bother trying to delete WAL files older than the .backup file? When PostgreSQL is in recovery mode it knows which WAL files are necessary to perform the recovery. Also, the documentation recommends excluding the pg_xlog directory when performing the base backup. Likely when it comes time to recovery the online WAL files have been archived already, so it is a risk of confusion I am sure. If the OP is doing the same as myself, the WAL files are being archived outside of pg_xlog (indeed outside of the PG data cluster) - it makes no sense keeping around WAL files older than the .backup file because they're not needed - in a day I generate ~5GB worth of WAL files which aren't needed after the full backup runs at 2am, so it's a waste of resources to keep them around or to worry about backing them up after this time. Andy.
Re: [ADMIN] Debug (is it PostgreSQL?)
A quick Google search reveals it looks like PgAdmin's job scheduling queue: http://archives.postgresql.org/pgadmin-support/2006-06/msg5.php Might be worth asking on [EMAIL PROTECTED] Andy. Ezequias Rodrigues da Rocha wrote: My Linux is reporting the following message: DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... DEBUG: Clearing inactive connections DEBUG: Connection stats: total - 1, free - 0, deleted - 0 DEBUG: Checking for jobs to run DEBUG: Sleeping... Can someone tell me what is it ? !DSPAM:37,45d5ff7518515849972366! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834
Re: [ADMIN] PgAdmin : table backup problem
Tom Lane wrote: Cassiano, Marco [EMAIL PROTECTED] writes: If I understand well, PgAdmin fails in composing the correct pg_dump command. The switch combination -t collezioni -n anamat doesn't work. The manual says that the -n switch is ignored when -t is used The right (at least, working) syntax seems to be the following : /usr/local/pgsql/bin/pg_dump -U user -F c -v -f /tmp/collezioni.backup -t anamat.collezioni mdn This is something that was changed in 8.2. You seem to be using a PgAdmin that expects the pre-8.2 convention. If this is the latest version of PgAdmin then you need to complain to the PgAdmin developers (not here --- they have their own mailing lists). FYI (the OP's original e-mail was copied in to pgadmin-support) this is fixed in the upcoming PgAdmin 1.6.3.
Re: [ADMIN] rename a cluster
The cluster's name is only the file-system's directory path. You can easily rename this directory, as long as you pass the relevant directory name to pg_ctl's -D parameter (which tells PostgreSQL which database cluster to use.) It goes without saying, don't move the directory while PostgreSQL is running. Ray Stell wrote: On Thu, Feb 15, 2007 at 06:36:23PM +0100, Peter Eisentraut wrote: Ray Stell wrote: Is there a method to rename a cluster? Clusters don't have names in the first place. So, from 16.2 Creating a Database Cluster ...you must initialize a database storage area on disk. We call this a database cluster. then, directory = cluster, no? What I was trying to get at is, is mv of the dir ok or is there something else that needs to be considered? Thx. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster !DSPAM:37,45d4a8511420134615! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834
Re: [ADMIN] rename a cluster
It comes under Server Startup: http://www.postgresql.org/docs/8.2/static/server-start.html Ray Stell wrote: Many thx. It that in the doc? On Thu, Feb 15, 2007 at 06:21:05PM +, Andy Shellam (Mailing Lists) wrote: The cluster's name is only the file-system's directory path. You can easily rename this directory, as long as you pass the relevant directory name to pg_ctl's -D parameter (which tells PostgreSQL which database cluster to use.) It goes without saying, don't move the directory while PostgreSQL is running. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Re: Θέμα: Re: [ADMIN] possi ble Bug in windows version ?
If you're running one of Windows XP SP2 or Windows Server 2003, the Windows Firewall will be blocking the connection. If you have the Windows Firewall option in Control Panel, check it's disabled, then try again. If that cures your problem, add an exception in for port 5432 before you turn the firewall back on. Andy. gpio Oxxce wrote: Hi Paul, All, My host pg_hba.conf: host all all 127.0.0.1/32 md5 hostall all 192.168.1.1/32 trust hostall all 192.168.1.2/32 trust and postgresql.conf: listen_addresses = '*' port = 5432 max_connections = 100 the 192.168.1.1 is also the server IP, and I am tryning to connect from the 192.168.1.2 with pgExlorer and pgAdmin but I get a message : Cannot connect to Server. Connection timeout . Chech if the server is running and accepting TCP connections on 5432 I will try to setup the database and see if this happens also the binary distribution without the installer. Thx George */Paul Lambert [EMAIL PROTECTED]/* έγραψε: gpio Oxxce wrote: Hi all, After downloading and installing the latest Postgresql server (postgresql-8.2.3-1..zip ) I can not make it accept local IP connections! I edit the |pg_hba.conf |file with host all all 192.168.100.1/32 trust but still I can't connect from 192.168.100.1 to the DB. Also during the installation I checked the option Accept TCP connections.. (or something like that) Am I missing something or it is a possible bug ? Thanks George In putting in the above line did you replace the host line with address 127.0.0.1/32? If so, that's probably your problem - it needs the 127* line. Is postmaster listening on the default port (5432) or did you change that during install? (Can check that in the postgresql.conf file in the same place as pg_hba.conf) How are you trying to connect? (pgAdminIII, some other program using the ODBC driver?) What error are you getting? NB: I get access denied if I put my servers IP address into the pg_hba.conf file and comment out the existing 127 address line. Local connections would not go out through the network and would thus not be seen by PG as coming from your 192/168 address. P.. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Χρησιμοποιείτε Yahoo! Βαρεθήκατε τα ενοχλητικά μηνύ ματα (spam); Το Yahoo! Mail διαθέτει την καλύτερη δυνατή προστασία κατά των ενοχλητικών μηνυμάτων http://login.yahoo.com/config/mail?.intl=gr Χρησιμοποιείτε Yahoo! Βαρεθήκατε τα ενοχλητικά μηνύ ματα (spam); Το Yahoo! Mail διαθέτει την καλύτερη δυνατή προστασία κατά των ενοχλητικών μηνυμάτων http://login.yahoo.com/config/mail?.intl=gr !DSPAM:37,45d2ce7b18511494436004! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834
Re: [ADMIN] Postgres is not starting or stopping
You will always have a postmaster binary - do a find / -name postmaster to find it. If your secondary production server hasn't gone live yet, I'd seriously consider starting it up on the 8.2 series. Manish Pillai wrote: I have installed it using rpm..So bin folder is not available inside pgsql :-( Manish */Shoaib Mir [EMAIL PROTECTED]/* wrote: Instead of starting with the service, trying starting up with 'postmaster' binary. It can be found in the database server 'bin' folder and that should give a little more detail failure message. To enable logging please go through -- 16.4.5. Error Reporting and Logging at http://www.postgresql.org/docs/7.4/interactive/runtime-config.html -- Shoaib Mir EnterpriseDB ( www.enterprisedb.com http://www.enterprisedb.com/) On 2/2/07, *Manish Pillai* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi, I am getting only this much... [EMAIL PROTECTED] init.d]# ./postgresql restart Stopping postgresql service: [FAILED] Starting postgresql service: [FAILED] [EMAIL PROTECTED] init.d]# How to enable log in postgres...Please help Regards Manish */Shoaib Mir [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]/* wrote: If you can tell the specific error on start/stop then someone might be able to help you with this... -- Shoaib Mir EnterpriseDB (www.enterprisedb.com http://www.enterprisedb.com/) On 2/2/07, *Manish Pillai* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi all, I have two production server .One ia already live and the other is going to live soon. In the 1st server i have postgres 7.4.6 and having no problem In the second server we have postgres (PostgreSQL) 7.4.15. Here i have made a mistake. I have removed postgresql..conf and pg_hba.conf and kept the same files from the first server (7.4.6). After this process the postgres is not able to start/stop. Then i removed the two files which i have copied from the first server and copied the the orginal files to the exact place (/var/lib/pgsql/data).But still the database is not able to start or stop.. Please help if u have any idea Regards Manish Need a quick answer? Get one in minutes from people who know. Ask your question on Yahoo! Answers http://answers.yahoo.com/;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTEwOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx. Want to start your own business? Learn how on Yahoo! Small Business. http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites http://farechase.yahoo.com/promo-generic-14795097;_ylc=X3oDMTFtNW45amVpBF9TAzk3NDA3NTg5BF9zAzI3MTk0ODEEcG9zAzEEc2VjA21haWx0YWdsaW5lBHNsawNxMS0wNw-- to find flight and hotel bargains. !DSPAM:37,45c34f2c118211085466303! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834
Re: [ADMIN] unsubscribe
You might have more luck here: http://archives.postgresql.org/pgsql-admin/ GURON Rawender wrote: Before printing, please consider the environment. IMPORTANT NOTICE: This e-mail and any attachment to it are intended only to be read or used by the named addressee. It is confidential and may contain legally privileged information. No confidentiality or privilege is waived or lost by any mistaken transmission to you. The RTA is not responsible for any unauthorised alterations to this e-mail or attachment to it. Views expressed in this message are those of the individual sender, and are not necessarily the views of the RTA. If you receive this e-mail in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. !DSPAM:37,45c11c5f118218703124696! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834
Re: [ADMIN] Another way to Replicate
Chad Wagner wrote: On 1/19/07, *Alexander B.* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I would like to know if is possible to replicate in postgres applying binary logs (wal logs) like the same way is done on Oracle!! Did anybody tried to do that? You could do it, yes, there was a PITR High Availability project in PgFoundry (http://pgfoundry.org/projects/pgpitrha/) launched a while ago, but this doesn't seem to have released any files (I thought I remembered that they did release a test snapshot.) The basic principle for pre-8.2 servers is that your master sends its WAL log files to your slave. You add a base backup on your slave and start it in recovery mode. Your slave's restore_command script checks for the next file in the sequence (passed as a parameter from PostgreSQL) - when it's received, it copies into the xlog directory and passes a success return code to PGSQL. Before the file is received, the script sits in a loop that checks every minute or so for a new file, which will keep PostgreSQL waiting for it. When the file comes in, it copies it and passes a success code back. You'd then have to build in a mechanism so you can touch a file if you want to bring PostgreSQL up out of recovery mode, which terminates the script with a non-zero code so PostgreSQL knows it's received all files and starts up. Oracle doesn't use binary logs for replication, for regular snapshots it uses a materialized log -- which is nothing more than a table with a primary key and what type of change. If we are talking multi-master replication, then Oracle uses Advanced Queues (essentially another table) and pushes the data. All off this occurs over DB links. As for binary (archived redo) logs in Oracle, they can be used for a Hot Standby. Which PostgreSQL also supports, as I understand it this is a new feature for 8.2. Yep, warm standby is in 8.2, I've not investigated it, though. I personally haven't investigated Slony, but I believe it functions similar. Applying the simple ideia: - for each archived Wal logs, transfer to slave server; - after transfer, apply recover on postgres; - repeat the steps above, all the time; Again, this is a hot (warm in PostgreSQL) standby database. I don't think you can bring online a database in read only while it is actively applying the archived WAL logs. No, while the recovery is taking place, users trying to connect will get a FATAL: Database system is starting up error. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Another way to Replicate
Andy Shellam (Mailing Lists) wrote: Chad Wagner wrote: On 1/19/07, *Alexander B.* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I would like to know if is possible to replicate in postgres applying binary logs (wal logs) like the same way is done on Oracle!! Did anybody tried to do that? You could do it, yes, there was a PITR High Availability project in PgFoundry (http://pgfoundry.org/projects/pgpitrha/) launched a while ago, but this doesn't seem to have released any files (I thought I remembered that they did release a test snapshot.) Ah, just found it - it's in CVS - http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgpitrha/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Incremental Backup of a particular database
I believe the option for a warm standby (which sounds like it is what you need) was made available in 8.2.0. You'd have to look at the manual though, I haven't had time to investigate, and I'm not sure whether it's cluster-wide, or database-specific. Yogvinder Singh wrote: Hi Ppl, I have Two PostgreSQL Database Servers. The situation is like this:- 1) On first database server ball the operations (i.e live server) will be done. This server may contain n number of databases. 2) On the second database server, i want to keep one particular database (from the first database server ) and keep it updated to the latest from the First Database Server. I just want to be able to take some sort of incremental backup from the first server and restore it to the second server. Is this situation feasible. How can i take an incremental backup of a particular database out of many databases? On which version this is supported? IF not supported, is there any alternative way to handle this situatio. Regards, Yogvinder Singh, !DSPAM:37,45ae2b2f137105304223843! Disclaimer :- This e-mail message including any attachment may contain confidential, proprietary or legally privileged information. It should not be used by who is not the original intended recipient. If you have erroneously received this message, you are notified that you are strictly prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd and / or its subsidiary Companies accept no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus and further acknowledges that any views expressed in this message are those of the individual sender and no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of Newgen Software Technologies Ltd and / or its subsidiary Companies, as applicable. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Incremental Backup of a particular database
Shoaib Mir wrote: Even with ver 8.1 you can use WAL archive backups (PITR) to implement an incremental backup strategy. Yes, true, but it's harder with 8.1 as you always have to start from a base backup and roll WAL logs forward - without a custom script you cannot wait for new WAL logs to arrive and apply them on top of a base backup. There is a project in PgFoundry to allow for warm-standby in the 8.0 and 8.1 series, however this is reportedly built-in to 8.2 now. - Shoaib Mir EnterpriseDB (www.enterprisedb.com http://www.enterprisedb.com) On 1/17/07, *Andy Shellam (Mailing Lists)* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I believe the option for a warm standby (which sounds like it is what you need) was made available in 8.2.0. You'd have to look at the manual though, I haven't had time to investigate, and I'm not sure whether it's cluster-wide, or database-specific. Yogvinder Singh wrote: Hi Ppl, I have Two PostgreSQL Database Servers. The situation is like this:- 1) On first database server ball the operations (i.e live server) will be done. This server may contain n number of databases. 2) On the second database server, i want to keep one particular database (from the first database server ) and keep it updated to the latest from the First Database Server. I just want to be able to take some sort of incremental backup from the first server and restore it to the second server. Is this situation feasible. How can i take an incremental backup of a particular database out of many databases? On which version this is supported? IF not supported, is there any alternative way to handle this situatio. Regards, Yogvinder Singh, Disclaimer :- This e-mail message including any attachment may contain confidential, proprietary or legally privileged information. It should not be used by who is not the original intended recipient. If you have erroneously received this message, you are notified that you are strictly prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd and / or its subsidiary Companies accept no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus and further acknowledges that any views expressed in this message are those of the individual sender and no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of Newgen Software Technologies Ltd and / or its subsidiary Companies, as applicable. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq !DSPAM:37,45ae3b61137103926781350! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] windows silent installer .msi file
Unzip the zip file - then you'll have the MSI package. Vasu Kamma (vakamma) wrote: Thanks Mr Radev. Instructions are given for .msi file , but the link has .zip file Could you help me how I can do the silent installation. Thanks Vasu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Milen A. Radev Sent: Friday, January 12, 2007 6:34 PM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] windows silent installer .msi file Vasu Kamma (vakamma) написа: please provide me the link for downloadable .msi file , for silent installation on windows machines. http://wwwmaster.postgresql.org/download/mirrors-ftp?file=binary%2Fv8.2.1%2Fwin32%2Fpostgresql-8.2.1-1.zip Then read this - http://pginstaller.projects.postgresql.org/silent.html. -- Milen A. Radev ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate !DSPAM:37,45a79d8913710645842! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] configuring the postmaster.log
Marc Mamin wrote: Hello, I'm confused about the logging destination parameters in postgresql.conf My Problem is that the postmaster.log is getting too large and I want to roll it (once per day) Following parameters apply to optional postgresql...log. log_rotation_age = 1440 log_filename = 'postgresql-%Y-%m-%d_%H.log' Is there a way to use a similar definition for the postmaster.log ? That is for the postmaster log - AFAIK there's only one log destination (syslog or a standard file.) When I generate postgresql...log files, are log messages written in both postmaster.log and postgresql.log ? I've never come across there being two files - log_filename says where log messages are written - there aren't two files (AFAIK). Have you got an stderr redirect that sends all postmaster output on stderr to your own log file? If so, set silent mode on the log configuration in your postgresql.conf to get the same effect - only it will appear in the main postgresql log file defined by log_filename. I guess I don't understand the meaning of these two different logging destination. Moreover, I'm catching the error stream in a perl application to log errors: $sth = $dbh-prepare( select ... ); $sth-execute ()|| print (STDERR PG error: . $sth-errstr .\n) exit 1; $sth-finish; Will I still get the postgres error in perl if I set redirect_stderr = on in postgresql.conf ? Setting redirect_stderr = on will mean that all errors that would normally appear on the console that started the Postmaster will be sent to log_filename instead.. -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Database Create Date
Bruno Wolff III wrote: On Wed, Jan 10, 2007 at 10:00:37 +0200, Achilleas Mantzios [EMAIL PROTECTED] wrote: Στις Τρίτη 09 Ιανουάριος 2007 18:10, ο/η Andy Shellam (Mailing Lists) έγραψε: Achilleas Mantzios wrote: I was able to find that in FreeBSD the -U in ls (1) does the job. However i could not find any inode creation time related info for linux (ext3). Anyone has any clue on that? I believe ls -l by default shows the created time, you can switch to show the last modified time using ls -l --time=atime Just another note on this, atime is the last access time. ctime is the real last modify time, mtime is another modify time that can be changed (which is useful after backups). atime is often disabled in ext3 file systems to reduce I/O, since it isn't all that useful. I thought it was as well to begin with - but in ls on Linux there's a separate atime and access value to the show time parameter in ls - so if atime is the last access time, what's access mean? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Database Create Date
Andy Shellam (Mailing Lists) wrote: Bruno Wolff III wrote: On Wed, Jan 10, 2007 at 10:00:37 +0200, Achilleas Mantzios [EMAIL PROTECTED] wrote: Στις Τρίτη 09 Ιανουάριος 2007 18:10, ο/η Andy Shellam (Mailing Lists) έγραψε: Achilleas Mantzios wrote: I was able to find that in FreeBSD the -U in ls (1) does the job. However i could not find any inode creation time related info for linux (ext3). Anyone has any clue on that? I believe ls -l by default shows the created time, you can switch to show the last modified time using ls -l --time=atime Just another note on this, atime is the last access time. ctime is the real last modify time, mtime is another modify time that can be changed (which is useful after backups). atime is often disabled in ext3 file systems to reduce I/O, since it isn't all that useful. I thought it was as well to begin with - but in ls on Linux there's a separate atime and access value to the show time parameter in ls - so if atime is the last access time, what's access mean? Hmm ok just done a bit of experimenting on this - atime and access show exactly the same information - mtime is an invalid value according to my Fedora 5 system. Confused. -- start paste -- ls -l / --time=mtime ls: invalid argument `mtime' for `--time' Valid arguments are: - `atime', `access', `use' - `ctime', `status' Try `ls --help' for more information. -- end paste -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Database Create Date
Negandhi, Nishith wrote: Hi, I need to know the create date of databases on PosegreSQL. PG_DATABASE does not provide this information. Are there any other system catalogs that can provide the above information?? Thanks !DSPAM:37,45a3b385137101969839670! I'm sure there is (not known to me atm), but for a quick shot you could find the DB's OID from pg_database and look at the create date of pg data dir/base/db oid ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Database Create Date
Achilleas Mantzios wrote: I was able to find that in FreeBSD the -U in ls (1) does the job. However i could not find any inode creation time related info for linux (ext3). Anyone has any clue on that? I believe ls -l by default shows the created time, you can switch to show the last modified time using ls -l --time=atime Also in FreeBSD (at least, 6.1) there is no -U option to ls; there is a -u option but this shows the last access, not the creation time. -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] Recovering a deleted database problem
Earlier this evening I made the usual mistake someone makes at some point in their lives - and dropped a database thinking I didn't need it, then realised later I did. So, because I have DDL statement logging turned on, I could find the exact time/date it happened, and attempted to restore from my file-system level backup taken at 2am this morning, and rolled forward all my WAL logs archived throughout the day (98 files.) In the recovery.conf, I specified the date/time from the log file that the database was dropped and set recovery_target_inclusive so it would not include this transaction. However the restore has finished, and PostgreSQL thinks the database is there, but the relevant data directory in base is missing - so it's removed the file-system objects but not the system database entry. I've checked the base backup, and this directory is in the backup, hence it has been removed at some point during the restore. What I'm going to do now is to set the recovery target to about a minute earlier to make sure the transaction has not started when the recovery finishes - but I'm just asking if I'm missing something obvious, as this is the first time I've done a restore from WAL logs. (Note, after writing this, I tried restoring to a minute earlier (ie. 18:57:40) and still have the same problem. As a quick fix, I copied the base/35290 directory from the backup before I had run the recovery - does anyone know any caveats to doing this, as the DB seems to be working OK?) My recovery.conf is: # PostgreSQL database recovery config file restore_command = 'cp /path/to/wal/archive/%f %p' recovery_target_time = '2007-01-04 18:58:40 -00:00' recovery_target_inclusive = 'false' The log entry where I discovered the date/time is: 2007-01-04 18:58:40 GMT 84.45.66.158 postgres postgresql - LOG: statement: DROP DATABASE [dbname]; The error I get when I try to connect to [dbname] after the restore is: FATAL: database [dbname] does not exist DETAIL: The database subdirectory base/35290 is missing. But the [dbname] database is still in the system catalogues: /usr/local/pgsql/bin/psql -U postgresql -d postgres -c select datname from pg_database; datname postgres [db1] template1 template0 [dbname] [db2] [db3] [db4] (8 rows) This is PostgreSQL 8.1.5 on FreeBSD 6.1. Many thanks, -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Connectionstring
Doing a quick Google search, it appears to be, you add *Encoding*=UNICODE or whatever you want your encoding to be, in your connection string. Try the pgsql-interfaces list - that's more appropriate for this sort of thing. Sistemas C.M.P. wrote: How can I specify the Encoding in the connectionString using pgOleDB with Visual Basic.? !DSPAM:37,459e5d08137101549039207! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Connectionstring
Excuse the asterisks - they were added in by my mail client - it should be Encoding=UNICODE Andy Shellam (Mailing Lists) wrote: Doing a quick Google search, it appears to be, you add *Encoding*=UNICODE or whatever you want your encoding to be, in your connection string. Try the pgsql-interfaces list - that's more appropriate for this sort of thing. Sistemas C.M.P. wrote: How can I specify the Encoding in the connectionString using pgOleDB with Visual Basic.? -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Connectionstring
Hmm OK was worth a shot - probably best bet would be to ask on pgsql-interfaces. Andy. Sistemas C.M.P. wrote: With or without asterisks it doesn't work. This string work on ODBC but not with pgOLEDB - Original Message - From: Andy Shellam (Mailing Lists) [EMAIL PROTECTED] To: pgsql-admin@postgresql.org Sent: Friday, January 05, 2007 11:24 AM Subject: Re: [ADMIN] Connectionstring Excuse the asterisks - they were added in by my mail client - it should be Encoding=UNICODE Andy Shellam (Mailing Lists) wrote: Doing a quick Google search, it appears to be, you add *Encoding*=UNICODE or whatever you want your encoding to be, in your connection string. Try the pgsql-interfaces list - that's more appropriate for this sort of thing. Sistemas C.M.P. wrote: How can I specify the Encoding in the connectionString using pgOleDB with Visual Basic.? -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.16.6/617 - Release Date: 05/01/2007 11:11 a.m. !DSPAM:37,459e6166137101868784367! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Recovering a deleted database problem
Thanks for the info Tom, too much data will have been entered into the other databases in the cluster by now so I cannot give it another shot on that server, plus all of yesterday's WAL logs will have been purged by now by the daily backup routine. Is it enough to simply have re-copied in the base/xxx directory from the base backup, after the PITR recovery had completed (obviously any changes made to that database since the base backup won't have been restored but thankfully it's backed up nightly and doesn't change too often :-) ) All CRUD operations seem to be working on that database OK and the app that (I now know) uses it hasn't complained. What I'll probably do is try to simulate the same process again on a different machine to get myself a bit more familiar. Is there any other situations you can think of where this may also be relevant, or is it just when dropping a complete database? Many thanks, Andy. Tom Lane wrote: Andy Shellam (Mailing Lists) [EMAIL PROTECTED] writes: (Note, after writing this, I tried restoring to a minute earlier (ie. 18:57:40) and still have the same problem. The PITR recovery process in effect rolls forward until it finds a transaction-commit record = the specified time. Now for normal database operations, stopping just short of the commit of the transaction is enough to ensure that the transaction has no effect. But for the XLOG_DBASE_DROP record, not so --- replaying that means rm -rf base/whatever. So you've got to make sure the replay stops before it reaches that record, and that means you need a stop time = the commit time of some *prior* transaction. I suppose this was a slow time of day and you didn't have any other commits in the prior minute :-( ... so take another look in the log and see what was the last commit before that, and use that time. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster !DSPAM:37,459e6a32137101648020742! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Can't See Data - Plz Help!
Hi Jeanna, Does pgAdmin give you back any error, like permission denied, or anything like that? Can you see all the properties of the table, such as indexes, tables etc before you open it? As it's happening on various PCs and versions of pgAdmin, I'd hazard a guess that it's server-side, but I'm not sure. Also have you tried any other client tools? EMS do a good PGSQL Manager for free (the Lite version) - and you could use that to determine if the problem is with the server or the client application, a different tool may also highlight an error that pgAdmin does not. EMS is at www.sqlmanager.net. Might be worth asking on pgadmin-support@postgresql.org as the developers of pgAdmin can have a look-see too and might be able to suggest other ideas. Regards, Andy. Jeanna Geier wrote: Hi List! I'm really in need of some guidance here.. We're running PostgreSQL 8.0 and I have PGAdmin v.1.4.3 on my local pc and version 1.2.2 on my server the other developer's pc - when I open PGAdmin to connect to the database(s), I can do so without any problems, however, when we go to view the data in the database(s), we cannot see anything, the window opens with the menu bars, but there are no column names, and no data. And if I try a 'Refresh', it appears to do something, but still nothing is displayed. If I use a command prompt and connect to the db's, I can select from the tables and everything returns OK; and using our program, which connects to the db's using JDBC, it's connecting and returning data OK... However, not being able to view the data in the tables and views is an issue in our development and testing (not to mention sanity). It's happening on different databases, on multiple pc's, with different versions of the Admin tool. We haven't done any updates to either the database, our version of Postgres, or the Admin tool any thoughts??? Thanks in advance for your time and help! -Jeanna ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster !DSPAM:37,459e7bd3137101637590987! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Can't See Data - Plz Help!
One other thing I've just thought of, if you issue a manual query from within pgAdmin - does this succeed? Also roughly how big are the tables (i.e. number of rows) - does it help if you set a LIMIT in the SQL clause (by default I think it's 1000 rows but try setting a LIMIT of 1 row and see if that comes back.) Andy. Jeanna Geier wrote: Thanks for the reply, Andy. No, no error from pgadmin, and, yes, I can see all the properties of the tables before opening it. You can open the tables and see menu bars and what-not, just no data in the tables/views, but like I said, I know the data is in there, because I can access it using psql from the command line. We haven't tried any other client tools, but I'll give that a try, thanks. And, if that doesn't help, I'll take your suggestion of asking on pgadmin-support. Thanks again!! -Jeanna ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Windows Dependency Issue
Hello Pete, There is certainly a Windows command you can run that will alter the command used to start a service - it's sc (which can also be used to start/stop a service programmatically.) The command syntax to modify the EXE path is: sc config service name binpath= path to EXE (eg.) sc config PostgreSQL Database Server version binpath= C:\Program Files\PostgreSQL\bin\postmaster EXE and flags If it's successful, you'll get the message: [SC] ChangeServiceConfig SUCCESS Also note there's a space between binpath= and the EXE name - that took me a while to figure out! Hope this can be of use. Andy. Peter Egan wrote: Hello, We have a windows-based server that uses postgres (8.1) as the db. We use a 'silent install' to install postgres as a windows service. Our server is then installed as a service with a dependency on postgres. As seen in other posts, to ensure that postgres starts up before the server starts, one should use the pg_ctl command with the '-w' flag. When installed as a service, the '-w' flag isn't set. My question - is there a way to set this '-w' flag through the silent install process? Or is there another way to accomplish this without requiring the user to set this after the installation process? Any help would be appreciated. Thanks, Pete !DSPAM:37,459d40d631947254553576! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] How do you upgrade for production servers?
If upgrading between minor versions in the same series (e.g. 8.1.3 - 8.1.5) you can simply use the same data directory. However, if the major version changes (e.g. 8.1.x - 8.2.x), you must: 1. Dump the databases from your old server (preferably using the new version client, I believe) while your old server is still running 2. Stop your old version 3. Start your new version 4. Restore the backup into your new version For the minimum downtime, the best thing to do is get the 2 servers running together (e.g. run your new version on port 5433) - then dump your old database, stop your old server (so no updates can get in after your backup), restore the backup into the new server, and restart your new server on port 5432. It all depends on how big your databases are, and the length of time it'll take to restore your backup. Happy New Year to you too. Andy. Arnau wrote: Hi all, This is a general question about which procedure you follow when you upgrade your productions servers. Let's say we have a server running and older version and I want to install the latest version on the same server. Do you install slony? do you dump all the databases, install the latest version and after you restore? any other procedure? I'd like to have my production server down as less time as possible. Thank you very much and have a great new year :D -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Postgres and Firewall
Chetan Parekh wrote: We have Postgress Database running on machine with ip address 192.168.0.1 on port no 5432. We want to access this database from outside the firewall. Hence we need to open port no 5432 of 192.168.01 in firewall for outside users. But in firewall configuration we need to provide the type of call also like https, https, ect. We have Swing based application that need to access database over internet. Please guild me, what kind of call it will be from Swing based application to database !DSPAM:37,45954a6a31941048546528! It's a PostgreSQL client library using the PostgreSQL protocol that does the talking. You sound like you're using a fairly restrictive firewall, generally allowing traffic from/to a certain IP and port number is all that is required - is there a generic TCP option at all in your firewall? -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Frustrating LO problem
Hi Sean, I'm certainly not a PostgreSQL expert when it comes to large objects etc, but there's one thing that jumps out at me here (perhaps someone else can see the same line of thought and into more depth as I can't think of much else to suggest?) The logfile lines when it doesn't: { WARNING: nonstandard use of \\ in a string literal at character 94 HINT: Use the escape string syntax for backslashes, e.g., E'\\'. LOG: statement: insert into doc.templates (description, last_update, template) values ('test', '12/29/2006', 'testfile\\056contents') ERROR: invalid input syntax for type oid: testfile\056contents ^^ here it seems to be thinking that column 'template' is of type 'oid', not 'lo' (if I'm reading the error right: invalid input syntax for type oid). Just double-check what data-type that column is in this database. Regards, Andy. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Dump and Query
Hi Enrico, The following command will get you a text file of your result-set: # echo SELECT customer_id, first_name, sur_name FROM users;|/usr/local/pgsql/bin/psql -U [username] -d [database] myfile.txt # cat myfile.txt customer_id | first_name | sur_name -++-- CUS002 | Andy | Shellam Or you can dump a specific table: # /usr/local/pgsql/bin/pg_dump --table=[tablename] [database] Regards, Andy. Enrico wrote: Is there any way to make a dump from a query? For example if my query is: select field1,field2 from table Does it exist a shell command like pg_dump --QUERY myquery -f myfile? Have a nice day Enrico -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(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: [ADMIN] DB problem
Vinayak, Please don't post twice to two different lists (four of the same posts to both pgsql-admin and pgadmin-support.) This clearly isn't a problem with PG Admin - so don't clutter that list up, and don't multiple-post - it won't change anything posting twice, and if anything you'll only end up annoying people so they won't feel obliged to help you anyway. In answer to your question, it took me 3 minutes to find this manual page: http://www.postgresql.org/docs/7.3/static/runtime-config.html There is no log_directory setting, but it is in 8.x (http://www.postgresql.org/docs/8.0/static/runtime-config.html), so your version doesn't support it. It would be helpful if you consult the manual for your running version, not the current release, as features inevitably get added and others change. And, as someone's already said, you should be thinking of upgrading to at minimum 8.1. Andy. [EMAIL PROTECTED] wrote: Hi, We are using PostgreSQL 7.3 version, installed in linux system. Only DB is getting shut down 3-4 times a day. From my application end I get Sorry too many clients error. Refering the FAQ I maximized the DB session limit to 200. I think the logs are not enabled in my DB. So I set the following options in postgresql.con file log_timestamp= true log_directory=pg_log log_filename=%Y-%m-%d_%H%M%S.log log_disconnections=true log_hostname=true log_statement=true After restarting the system got the following error : [EMAIL PROTECTED] root]# su postgres bash-2.05b$ postmaster -i -D /var/lib/pgsql/data FATAL: postgresql.conf:131: syntax error, token=% bash-2.05b$ postmaster -i -D /var/lib/pgsql/data FATAL: postgresql.conf:131: syntax error, token=% bash-2.05b$ postmaster -i -D /var/lib/pgsql/data FATAL: postgresql.conf:131: syntax error, token=% bash-2.05b$ postmaster -i -D /var/lib/pgsql/data FATAL: 'log_directory' is not a valid option name bash-2.05b$ postmaster -i -D /var/lib/pgsql/data FATAL: 'log_directory' is not a valid option name bash-2.05b$ postmaster -i -D /var/lib/pgsql/data FATAL: 'log_filename' is not a valid option name bash-2.05b$ postmaster -i -D /var/lib/pgsql/data FATAL: 'log_disconnections' is not a valid option name bash-2.05b$ postmaster -i -D /var/lib/pg When you install the DB, the logs are enabled by default? Would you pls tell me still what setting need to be done? Thanks, Vinayak V. Raikar Extn : 143 !DSPAM:37,4590b56931941914011633! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] pgAdmin crashes
Hi Bobby, I'd ask on the pgadmin-support list - PgAdmin's developers live there more-so than here. Regards, Andy Bobby Gontarski wrote: I am experiencing pgAdmin (windows xp) crash whenever I try to connect to the postgresql server (type password and hit enter). It started after installing the new 8.2 version it didn't do it with the earlier versions. What do I have wrong? Thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster !DSPAM:37,45913b9f31942113013226! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] pgAdmin crashes
As with Bobby, I advise you to post on pgadmin-support. This list is for support with the server administration. You might also want to provide pgadmin-support with details such as your server version, client version, PgAdmin version, platform, steps to reproduce the problem etc. It'll give them more to go on and they'll be more likely to answer you. Thanks, Andy. Iannsp wrote: Andy Shellam (Mailing Lists) escreveu: Hi Bobby, I'd ask on the pgadmin-support list - PgAdmin's developers live there more-so than here. Regards, Andy Bobby Gontarski wrote: I am experiencing pgAdmin (windows xp) crash whenever I try to connect to the postgresql server (type password and hit enter). It started after installing the new 8.2 version it didn't do it with the earlier versions. What do I have wrong? Thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Hi, to add more one preocupattion with pgadmin, i am experiencing crashs when I use to create SProc with the wizard. -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Backup
This could be implemented as a fairly simple script that does: - Specify a base backup name (e.g. with date/time) - Connect to postgres database - Issue pg_start_backup('base backup name') - Tar the data cluster directory, excluding pg_xlog - Issue pg_stop_backup - Compress the created archive - Move the created archive to the backup history folder (or tape-drive etc) - Find the date/time stamp of the latest .backup file in the xlog archive - Remove any WAL files created earlier than this .backup file (excluding the first WAL file needed - specified as START_WAL_LOCATION (within the .backup file) This process runs on our main PGSQL server at 2:00 in the morning and finishes within 4 minutes (on a 2GB database), and our backups can restore reliably - we have a base backup from 2:00am, and WAL files maintained throughout the day. We're getting a new WAL file archived roughly every 3-6 minutes, so should our server crash (or we feel the need) we can restore up to 6 minutes ago. The archive_command is just a simple copy - cp %p /path/to/archive/%f There's really nothing difficult, it puts you (the administrator) in control of every aspect of your backup which is a good thing. If this is it, then I'll end up with an old level zero (i.e. full, base ) backup and A LOT of level 1 (i.e. transaction log) backup. I think it should be more like it's for Informix, where you ask the database to perform an online level 0 backup (base); after this, it stores transaction logs on disk, which you can archive with level 1 backup. Then, say everyonce in a week, you get another level 0 backup, and the database clears the already-archived logs from disk and starts all over. This to me is exactly the same approach. If you really want to, you can tell PostgreSQL to do an online backup, wait a week (saving all WAL logs), then perform another online backup and remove the previous week's log files - it's just you'll end up with a lot of log files (dependent on transaction frequency and archive settings.) You don't have to have one base backup and then a ton of log files, because it'll take you forever to restore it, in fact the more often you perform a base backup the better (I had to restore a 2GB database a while backup, with a week's worth of WAL files, and it took 10 hours!) a lack of -i in cp, for example FWIW, -i in copy won't render a backup useless - only if the file to be archived has already been archived (in which case it stands a chance it's the same file anyway as PG rotates/recycles WAL file numbers.) Hope this goes some way to helping you out, Andy. Eduardo J. Ortega wrote: Hi: Well, I don't really like the fact that admin has to specify the archiving and restoring command; an error here (a lack of -i in cp, for example) may render the backup useless. In addition, the backup is performed only everytime the WAL file is filled; i need to take consistent backups every hour or so, and I am not sure if that time represents more or less than a WAL file ( i could still measure that, i guess). Finally, as I understand, the WAL backup method works like this: 1) Take full base FS backup 2) get some way to copy WAL files If this is it, then I'll end up with an old level zero (i.e. full, base ) backup and A LOT of level 1 (i.e. transaction log) backup. I think it should be more like it's for Informix, where you ask the database to perform an online level 0 backup (base); after this, it stores transaction logs on disk, which you can archive with level 1 backup. Then, say everyonce in a week, you get another level 0 backup, and the database clears the already-archived logs from disk and starts all over. I guess this could be achieved with PG, but it requieres considerably more steps (pg_start_backup, pg_stop_backup, manually cleaning old log files which could be an error point), or I am getting something wrong. Besides, why do you need to tell the database to stop the backup? shouldn't it stop by itself when there's no more information to be archived? Perhaps if any of you has this method working, you could provide me with your archiving and restoring commands as well as a description of the whole process the way you implemented it. Thanks, Eduardo. On Wednesday 13 December 2006 02:39, Shoaib Mir wrote: To me PITR looks like a very nice solution for incremental backup and even they can serve as a warm standby. What exactly are the complications you see with WAL archiving? --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/13/06, Eduardo J. Ortega [EMAIL PROTECTED] wrote: Hi there: Are there any nice (official or third party) backup utilities for postgres? I have a database which is several GB, so pg_dumping it to file and then bzipping2 every hour is not really the way to go. I've read a little about using WAL for incremental backup, but it sounds a little complicated and *very* user-error prone. (Not sure if this is the
Re: [ADMIN] pgadmin firewall configuration
No, Pg Admin connects to 5432 as that is PostgreSQL's server port. One popular misconfiguration I've seen is that the connection/traffic is *to* port 5432 on the server, not from port 5432 on the client. So, depending on your firewall you need rule 1 on it's own (if it supports keeping state) or rule 1 AND 2: Rule 1: Allow from client IP to server IP destination port 5432 source port any Rule 2: Allow from server IP to client IP destination port any source port 5432 If that doesn't work, post the relevant section of your firewall configuration. Andy. Ronny Ritongadi (LINC Solution) wrote: I have a postgresql database at the server, with firewall protected. And I want to access the database remotely using pgadmin on the client side. I have configured the postgre configuration file in such way that the client can access the database remotely using pgadmin, but when I turn on the firewall (which I have open the port 5432 to the specific IP –client) it won’t connect. Is pgadmin use another port other than 5432? Regards, Ronny Ritongadi IT - Web Linc Group !DSPAM:37,457ef5d530861117320364! -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.430 / Virus Database: 268.15.7/569 - Release Date: 12/5/2006 3:00 AM -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] how to recover database back from /data folder [ Possibly
I think the above messages support the fact that the database was shutdown properly before the filesystem level backup. Can anyone kindly confirm it ? LOG: database system is ready Says it all - if it hadn't been, you'd have gotten LOG: database system was not shut-down cleanly, recovering or along those lines. I listed the tables and did some selects the data seems to be there. I think postgres was not starting for some peripheral issues not because that data folder was corrupted. BTW when i first started postmaster it gave an error related to too liberal permissions. Probably the original poster was doing the same mistake. Regds mallah. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster !DSPAM:37,457d667230861609539772! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] .bat file to access postgreSQL command line from C:\
For a long time, I did not know how to use notepad to save something with a suffix other than .txt and then someone showed me that if you psql.bat , and choose allfiles type, then it will save with the .bat ending. Or, when you go to Save As in Notepad, wrap the filename in quotes, e.g. File Save As pgsql.bat This will tell Notepad that you've already added the extension and don't need .txt adding on as well. -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Lost password to user postgres
Regarding point 1, Scott, the user is on Windows. Windows binaries are available at www.postgresql.org and a lot has changed in the last 2 years, so if you do go down the re-install route, I'd recommend upgrading, although you'll have to dump your old data out of the server first, then restore it in to the new version. Regarding passwords, are you talking about the service account for the PostgreSQL database, or the postgres user in the database server? You can change the service account using the Local Users Groups control panel (run lusrmgr.msc) but you'll also need to change the logon user in the Services control panel for the PostgreSQL Database Server service. If you're talking about the postgres user, edit your pg_hba.conf file (Start Programs PostgreSQL [version] Edit pg_hba.conf (or there-abouts.) Add (or change if it already exists) a line (if it doesn't exist, add it before any other host lines) host all all 127.0.0.1/32 trust And restart the PostgreSQL service. Then when you connect to 'localhost', you'll always be granted access. Login, change the postgres user password, then edit your pg_hba.conf file again, to: host all all 127.0.0.1/32 md5 And restart the service, then you should be able to login with that password. Andy. Scott Marlowe wrote: On Sat, 2006-12-02 at 11:44 -0800, Philippe Salama wrote: 2 years ago, I installed postgresql on my desktop and used it for a while for learning purposes. Then, I stopped using it. I forgot the password to the user postgres You don't have to reinstall. you've got three basic options: 1. Change the password in single user mode 2. Edit pg_hba.conf and set it to trust mode, change your password, change back to md5 3. Re-initdb your cluster. For 1: su - postgres (from root if you have to) pg_ctl stop postgres template1 (or some other database you know exists) alter user postgres password 'newpassword'; For 2: su - postgres cd $PGDATA vi pg_hba.conf // change entries at bottom to trust pg_ctl reload psql template1 alter user postgres password 'newpassword'; For 3: su - postgres pg_ctl stop echo $PGDATA // make sure this is set to something like /var/lib/pgsql rm -rf $PGDATA/* initdb // with whatever options you need. I prefer option 2, as you don't have to take down your database to do it. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org !DSPAM:37,45720b9840411059761709! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Postgres at startup
I'm guessing by the postmaster.exe that this is on Windows. In that case, find PostgreSQL X.Y in Control PanelAdministrative ToolsServices (where X.Y is your major.minor version number of Postgres.) Change the services' start-up type to Manual (so you can start it yourself when needed.) Andy. Kis János Tamás wrote: 2006. November 8. 02.34 dátummal Michael McCloskey ezt írta: Postgres is starting automatically for me at startup. When I boot up my machine and then check the running processes I see postmaster.exe and 4 postgres processes. How can I stop this? PGBIN=/usr/local/pgsql/bin; PGDATA=/srv/postgresql; su postgres -c $PGBIN/pg_ctl -D $PGDATA stop I don't want Postgres starting up automatically. If you have Linux/Unix, you must to find a soft link in your /etc/rc[1-6].d dir and must to remove them: kjt:/# cat /etc/inittab | grep default id:3:initdefault: ^^^ kjt:/# ls /etc/rc3.d/ | grep postgres S20postgresql-my kjt:/# rm -i /etc/rc3.d/S20postgresql-my bye, kjt McAfee SCM 4.1 által ellenőrizve! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq !DSPAM:37,455c14a340413711078630! -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Checkpoint Location Format
Yup that sounds useful - would this be another column added to the pg_stop_backup called wal_filename or similar? My script uses this name to find the date/time of the current .backup file and remove any files earlier than it (except obviously the one listed in the START WAL LOCATION). I've yet to run a restore test, but the backup and automatic archive clearout is working nicely. I'm not too worried about the SQL function to find the current WAL file, although I can see this could be useful too. Thanks, Andy. Simon Riggs wrote: On Tue, 2006-11-07 at 13:12 +, Andy Shellam (Mailing Lists) wrote: I'm writing an automated file-system level backup application for use with WAL archiving, that will issue the pg_start_backup call, tar and gzip the cluster data directory, issue the pg_stop_backup call, and remove all previous un-needed WAL files from the archive. Is this any help? http://archives.postgresql.org/pgsql-patches/2006-05/msg00229.php If so, I'll see about updating it so it can get backpatched to 8.0 and 8.1 also. -- Andy Shellam NetServe Support Team the Mail Network an alternative in a standardised world p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Checkpoint Location Format
Hi Jim, Tom did answer actually! (Although it was first thing in the morning and he hadn't had any caffeine so he may have forgot to copy the list in ;) ) I forget what he said the format was now, and I haven't got his e-mail, but I've just done a regex to match one or more characters before and after the /, which works. Andy. Jim C. Nasby wrote: On Tue, Nov 07, 2006 at 01:12:53PM +, Andy Shellam (Mailing Lists) wrote: Hi, I'm writing an automated file-system level backup application for use with WAL archiving, that will issue the pg_start_backup call, tar and gzip the cluster data directory, issue the pg_stop_backup call, and remove all previous un-needed WAL files from the archive. I need to write a regular expression that will search for the WAL filename and checkpoint location from the backup_label file, and just want to clarify that the checkpoint location will always be of the format: X/XX - where X is one of 0-9, A-F? And then the WAL .backup file that is generated in the archive, has a filename of the form: WAL_FILE.00XX.backup where WAL_FILE is the name of the STARTING WAL LOCATION directive in the backup_label file, and XX is the last 6 digits of the checkpoint (after the / )? I don't know the answer, but since no one's replied I suggest looking in the code. Looking at the source of pg_start_backup would probably be a good start, though I'm guessing the real answer is somewhere in the backend. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Checkpoint Location Format
Hi, I'm writing an automated file-system level backup application for use with WAL archiving, that will issue the pg_start_backup call, tar and gzip the cluster data directory, issue the pg_stop_backup call, and remove all previous un-needed WAL files from the archive. I need to write a regular expression that will search for the WAL filename and checkpoint location from the backup_label file, and just want to clarify that the checkpoint location will always be of the format: X/XX - where X is one of 0-9, A-F? And then the WAL .backup file that is generated in the archive, has a filename of the form: WAL_FILE.00XX.backup where WAL_FILE is the name of the STARTING WAL LOCATION directive in the backup_label file, and XX is the last 6 digits of the checkpoint (after the / )? Thanks, Andy. ---(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
[ADMIN] [Fwd: Checkpoint Location Format]
Further to the below e-mail, I've come across a slight problem. The starting checkpoint location in a backup I've just taken is 0/22A3190 (note: 7 digits after the /, not 6 as I first thought.) However the .backup file is called WAL_FILE.002A3190 (ie. it only takes the right-most 6 digits). Can someone confirm this is the correct case? Thanks, Andy. Original Message Subject:Checkpoint Location Format Date: Tue, 07 Nov 2006 13:12:53 + From: Andy Shellam (Mailing Lists) [EMAIL PROTECTED] To: pgsql-admin@postgresql.org Hi, I'm writing an automated file-system level backup application for use with WAL archiving, that will issue the pg_start_backup call, tar and gzip the cluster data directory, issue the pg_stop_backup call, and remove all previous un-needed WAL files from the archive. I need to write a regular expression that will search for the WAL filename and checkpoint location from the backup_label file, and just want to clarify that the checkpoint location will always be of the format: X/XX - where X is one of 0-9, A-F? And then the WAL .backup file that is generated in the archive, has a filename of the form: WAL_FILE.00XX.backup where WAL_FILE is the name of the STARTING WAL LOCATION directive in the backup_label file, and XX is the last 6 digits of the checkpoint (after the / )? Thanks, Andy. ---(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: [ADMIN] installation into virtual server
On a standard Windows client connecting to a 2003 server, you can run mstsc /console to force Windows to re-direct the console to you. Andy. [EMAIL PROTECTED] wrote: Hi, hope to not be OT. I'm trying to install PostGresql into a virtual sever, I've set the ConnectionName = Console as suggested. In the shell and by using the same shell I start the installer .msi of 8.2.1 verision, but after the third step I get the error the postgresql installer must be run on the system console, not in a terminal services session. The connection is via IE Browser as simulation of Terminal Services.The operating system is Win 2003. any idea ? thans in advance and best regards Giu -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Crea il tuo sito web dinamico con PHP e MySQL - VideoCorso professionale direttamente nel tuo computer. Trucchi e segreti Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5142d=20061101 ---(end of broadcast)--- TIP 6: explain analyze is your friend !DSPAM:37,454958e540412847611171! ---(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