Re: [GENERAL] [PERFORM] Why Postgres use a little memory on Windows.
Thanks for all help of everyone. I have tried to change effective_cache_size = 24GB and it run well. Tuan Hoang Anh
[GENERAL] Why Postgres use a little memory on Windows.
Hello I have a bad query on PostgreSQL 9.0.23 - 64bit - Windows 2012 R2 - 48GB Ram explain analyze select d.data_id, d.table_name, d.event_type, d.row_data, d.pk_data, d.old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, '' from sym_data d inner join sym_data_gap g on g.status='GP' and d.data_id between g.start_id and g.end_id where d.channel_id='sale_transaction' order by d.data_id asc; Here is result Nested Loop (cost=319.42..4879348246.58 rows=32820035265 width=1525) (actual time=64656.747..5594654.189 rows=3617090 loops=1) -> Index Scan using sym_data_pkey on sym_data d (cost=0.00..3671742.82 rows=3867095 width=1525) (actual time=9.775..12465.153 rows=3866359 loops=1) Filter: ((channel_id)::text = 'sale_transaction'::text) -> Bitmap Heap Scan on sym_data_gap g (cost=319.42..1133.51 rows=8487 width=8) (actual time=1.438..1.439 rows=1 loops=3866359) Recheck Cond: ((d.data_id >= g.start_id) AND (d.data_id <= g.end_id)) Filter: (g.status = 'GP'::bpchar) -> Bitmap Index Scan on sym_data_gap_pkey (cost=0.00..317.30 rows=8487 width=0) (actual time=1.436..1.436 rows=1 loops=3866359) Index Cond: ((d.data_id >= g.start_id) AND (d.data_id <= g.end_id)) http://explain.depesz.com/s/c3DT I have run vaccum full. Here is my PostgreSQL config shared_buffers = 2GB work_mem = 64MB maintenance_work_mem = 1GB wal_buffers = 256 effective_cache_size = 4GB checkpoint_segments = 256 wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 256 random_page_cost = 3.5 autovacuum_vacuum_threshold = 1000 autovacuum_analyze_threshold = 250 max_locks_per_transaction = 2000 When I check taskmanager, I found postgres process is user 4-5MB What happened with my PostgreSQL. Please help me Thank you in advance. Tuan Hoang Anh
Re: [GENERAL] Does anyone user pg-pool II on real production ? Please help me.
Thanks for all. I will try pgpool-II pgbouncer . Hope it is good solution. Tuan Hoang Anh On Wed, Jan 7, 2015 at 8:30 AM, Sameer Kumar sameer.ku...@ashnik.com wrote: On 6 Jan 2015 03:02, tuanhoanganh hatua...@gmail.com wrote: Hello everybody Does anyone user pg-pool II on real production ? Yes we have a customer using it in load balancing mode and another one using it with Watchdog for high availability. How many slave do you have? and how many size of database ? In both cases 1. But I believe pgpool can support more databases. Size of db should not matter but we have 50GB if database being used with pgpool. I need config my old retail system to support ~ 1500 user with 1000GB over 4 years. I want to try pgpool-II but don't found real system use it. I guess in addition to using pgpool you should also think about using pgbouncer as connection pooling agent (I would probably install it on application server and configure to connect to pgpool) My system use direct SQL SELECT query and a lot pg procedure. If there is a way you can specify different connection strings for these two type of queries then you can make most out of pgbouncer. Do you do any explicit transactions in these procedures? Can pgpool-II support load balance from SELECT my_procedure() ... Yes you can specify those on white list (provided they are readonly procedures). Please help me Thanks in advance. Sorry for my English.
[GENERAL] Does anyone user pg-pool II on real production ? Please help me.
Hello everybody Does anyone user pg-pool II on real production ? How many slave do you have? and how many size of database ? I need config my old retail system to support ~ 1500 user with 1000GB over 4 years. I want to try pgpool-II but don't found real system use it. My system use direct SQL SELECT query and a lot pg procedure. Can pgpool-II support load balance from SELECT my_procedure() ... Please help me Thanks in advance. Sorry for my English.
Re: [GENERAL] Oracle to PostgreSQL replication
You can test with SymmetricDS (www.*symmetricds*.org) On Thu, May 8, 2014 at 10:35 PM, tuanhoanganh hatua...@gmail.com wrote: You can test with SymmetricDS (www.*symmetricds*.org) On Thu, May 8, 2014 at 12:53 PM, Sameer Kumar sameer.ku...@ashnik.comwrote: Thanks alot everyone! I guess I will be exploring more on oracle foreign data wrapper. Has anyone tried using oracle_fdw with Oracle RAC? I am wondering how would it handle failovers.
[GENERAL] Re: PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
I installed pyodbc-3.0.7.win-amd64-py3.2.exe from http://www.lfd.uci.edu/~gohlke/pythonlibs/#pyodbc then it work well On Fri, Sep 27, 2013 at 10:00 AM, tuanhoanganh hatua...@gmail.com wrote: Hello all. I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64 bit. But my plpython function have ERROR: ImportError: DLL load failed: A dynamic link library (DLL) initialization routine failed. after import pyodbc My Python version is ActivePython-3.2.2.3-win64-x64, pyodbc version is pyodbc-3.0.2.win-amd64-py3.2. On windows 7 SP1 64bit everything run fine. Please help me. Thanks in advance. Tuan Hoang Anh
Re: [GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
I try install plpython on PostgreSQL 9.3, Ubuntu 12.04 64bit but it has
error when CREATE EXTENSION plpython3u;
2013-09-27 22:09:26 ICT LOG: database system was shut down at 2013-09-27
22:09:20 ICT
2013-09-27 22:09:26 ICT LOG: database system is ready to accept connections
2013-09-27 22:09:26 ICT LOG: autovacuum launcher started
Traceback (most recent call last):
File /usr/lib/python3.2/sysconfig.py, line 354, in _init_posix
_parse_makefile(makefile, vars)
File /usr/lib/python3.2/sysconfig.py, line 240, in _parse_makefile
with open(filename, errors=surrogateescape) as f:
IOError: [Errno 2] No such file or directory:
'/usr/lib/python3.2/config-3.2m/Makefile'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File /usr/lib/python3.2/site.py, line 549, in module
main()
File /usr/lib/python3.2/site.py, line 537, in main
known_paths = addusersitepackages(known_paths)
File /usr/lib/python3.2/site.py, line 269, in addusersitepackages
user_site = getusersitepackages()
File /usr/lib/python3.2/site.py, line 244, in getusersitepackages
user_base = getuserbase() # this will also set USER_BASE
File /usr/lib/python3.2/site.py, line 234, in getuserbase
USER_BASE = get_config_var('userbase')
File /usr/lib/python3.2/sysconfig.py, line 597, in get_config_var
return get_config_vars().get(name)
File /usr/lib/python3.2/sysconfig.py, line 494, in get_config_vars
_init_posix(_CONFIG_VARS)
File /usr/lib/python3.2/sysconfig.py, line 359, in _init_posix
raise IOError(msg)
IOError: invalid Python installation: unable to open
/usr/lib/python3.2/config-3.2m/Makefile (No such file or directory)
My python 3.2 is
tuan@tuan-VirtualBox:/$ python3.2 --version
Python 3.2.3
tuan@tuan-VirtualBox:/$ python3.2 --version
Python 3.2.3
tuan@tuan-VirtualBox:/$ python3.2
Python 3.2.3 (default, Apr 10 2013, 06:11:55)
[GCC 4.6.3] on linux2
Type help, copyright, credits or license for more information.
exit()
tuan@tuan-VirtualBox:/$
On Fri, Sep 27, 2013 at 11:53 AM, tuanhoanganh hatua...@gmail.com wrote:
If i run import pyodbc from python command and run pyodbc sql command,
everything work well.
Please help me.
Tuan Hoang Anh
On Fri, Sep 27, 2013 at 10:05 AM, John R Pierce pie...@hogranch.comwrote:
On 9/26/2013 8:00 PM, tuanhoanganh wrote:
I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64
bit. But my plpython function have ERROR: ImportError: DLL load failed: A
dynamic link library (DLL) initialization routine failed. after import
pyodbc
My Python version is ActivePython-3.2.2.3-win64-**x64, pyodbc version
is pyodbc-3.0.2.win-amd64-py3.2.
On windows 7 SP1 64bit everything run fine.
Please help me. Thanks in advance.
I just heard from a developer friend that a recent windows update broke
all KINDA dll-related things with Windows. maybe thats what is happening
here. maybe not. he hasn't gotten to the bottom of it yet.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
Hello all. I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64 bit. But my plpython function have ERROR: ImportError: DLL load failed: A dynamic link library (DLL) initialization routine failed. after import pyodbc My Python version is ActivePython-3.2.2.3-win64-x64, pyodbc version is pyodbc-3.0.2.win-amd64-py3.2. On windows 7 SP1 64bit everything run fine. Please help me. Thanks in advance. Tuan Hoang Anh
Re: [GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
If i run import pyodbc from python command and run pyodbc sql command, everything work well. Please help me. Tuan Hoang Anh On Fri, Sep 27, 2013 at 10:05 AM, John R Pierce pie...@hogranch.com wrote: On 9/26/2013 8:00 PM, tuanhoanganh wrote: I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64 bit. But my plpython function have ERROR: ImportError: DLL load failed: A dynamic link library (DLL) initialization routine failed. after import pyodbc My Python version is ActivePython-3.2.2.3-win64-**x64, pyodbc version is pyodbc-3.0.2.win-amd64-py3.2. On windows 7 SP1 64bit everything run fine. Please help me. Thanks in advance. I just heard from a developer friend that a recent windows update broke all KINDA dll-related things with Windows. maybe thats what is happening here. maybe not. he hasn't gotten to the bottom of it yet. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL run as process in windows
Hello all. I have more than 500 users connect to Postgres in windows 2008 R2. I change registry make postgres service support 512 connect. But is there any good way make postgres run as process not service. Is it safe run postgres as process in windows. Thank you very much (sorry for my English) Tuan Hoang Anh
Re: [GENERAL] PostgreSQL run as process in windows
On Thu, Jan 3, 2013 at 10:45 AM, Craig Ringer cr...@2ndquadrant.com wrote: Running PostgreSQL directly via pg_ctl, not as a service, will not change how it performs under load at all. It will not help you service more than 500 concurrent connections. You really need to put a connection pool in place to limit the number of concurrent workers. Look at PgBouncer or PgPool-II. As far as I know neither of them runs on Windows; you might want to think about a Linux box as a front-end. Thanks for your help. But when use PgBouncer, it run as service and may be it only support 512 connect (Because windows service only support default 125 connect, change registry windows can support 512 connect). Is there any other way? Thanks you very much (Sorry for my English)
Re: [GENERAL] PostgreSQL run as process in windows
On Thu, Jan 3, 2013 at 11:04 AM, John R Pierce pie...@hogranch.com wrote: of course, a pooler only works right if the client applications are modified to open a connection, do a transaction, and close the connection. if the clients continue to hold idle connections, the pooler won't do anything useful for you. :-( My application continue to hold idle connections.
Re: [GENERAL] PostgreSQL run as process in windows
On Thu, Jan 3, 2013 at 11:52 AM, Craig Ringer cr...@2ndquadrant.com wrote: What's your host Windows OS? Windows Server 2008 R2? Windows 7? I am runing Windows Server 2008 R2
Re: [GENERAL] PL/pgSQL debugger and PostgreSQL 9.2
Is there link to download pldbgapi.dll on postgresql 9.2 x64 on windows Thanks in advance Tuan Hoang Anh On Fri, Sep 14, 2012 at 9:29 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Fabrízio de Royes Mello wrote: Has it moved somewhere else? Look at the pldebugger git repository in [1]. Thanks! Laurenz Albe -- 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 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute
Thanks for your help I don't create and drop alot of tables.7 Here is my config version;PostgreSQL 9.0.6, compiled by Visual C++ build 1500, 64-bit autovacuum_analyze_threshold;250 autovacuum_vacuum_threshold;1000 bytea_output;escape checkpoint_segments;64 client_encoding;UNICODE custom_variable_classes;symmetric effective_cache_size;4GB enable_seqscan;off extra_float_digits;2 lc_collate;English_United States.1252 lc_ctype;English_United States.1252 listen_addresses;* log_destination;stderr log_line_prefix;%t logging_collector;on maintenance_work_mem;1GB max_connections;100 max_locks_per_transaction;2000 max_stack_depth;2MB max_wal_senders;5 port;5433 server_encoding;UTF8 shared_buffers;2GB shared_preload_libraries;$libdir/plugins/plugin_debugger.dll TimeZone;Asia/Bangkok wal_buffers;2MB wal_keep_segments;128 wal_level;hot_standby work_mem;64MB Tuan Hoang Anh On Fri, Jun 29, 2012 at 9:13 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: tuanhoanganh hatua...@gmail.com wrote: VACUUM ANALYZE pg_catalog.pg_attribute, pg_catalog.pg_type, pg_catalog.pg_depend run all time of day. Is it normal in Postgres? Do you create and drop a lot of tables (including temporary tables)? What are the results of running the query on this page?: http://wiki.postgresql.org/wiki/Server_Configuration How is overall performance on the machine? -Kevin
Re: [GENERAL] Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute
Yes I have more than 10 activity connect on the database. But VACUUM ANALYZE pg_catalog.pg_attribute, pg_catalog.pg_type, pg_catalog.pg_depend run all time of day. Is it normal in Postgres? Tuan Hoang Anh On Fri, Jun 29, 2012 at 1:34 AM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2012-06-28 at 21:41 +0700, tuanhoanganh wrote: Hello I am using PostgreSQL 9.0.6 64 bit on Windows 2003 64bit. When i view Postgresql status, there are some autovaccum alway run. Ex VACUUM ANALYZE pg_catalog.pg_attribute. Is it problem of PostgreSQL? Please help me. Do you have activity on the database? If so, autovacuum is normal. Regards, Jeff Davis
[GENERAL] Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute
Hello I am using PostgreSQL 9.0.6 64 bit on Windows 2003 64bit. When i view Postgresql status, there are some autovaccum alway run. Ex VACUUM ANALYZE pg_catalog.pg_attribute. Is it problem of PostgreSQL? Please help me. Tuan Hoang Anh.
Re: [GENERAL] pgstat wait timeout
I have same problem too. Tuan Hoang Anh On Thu, Apr 19, 2012 at 10:43 PM, Efraín Déctor efraindec...@motumweb.comwrote: Any help will be appreciated. Thanks. *From:* Efraín Déctor efraindec...@motumweb.com *Sent:* Wednesday, April 18, 2012 1:32 PM *To:* pgsql-general@postgresql.org *Subject:* pgstat wait timeout Hello list: Today I started to see this messages on the PostgreSQL log: 2012-04-18 00:01:05 UTC : @ :WARNING: 01000: pgstat wait timeout 2012-04-18 00:01:05 UTC : @ :LOCATION: backend_read_statsfile, pgstat.c:3807 I searched and to fix it it was recommended to disable autovacuum, I did it and it worked, but how can I fix it without disabling autovacuum?. Thank you.
[GENERAL] PostgreSQL pgstat wait timeout question
I am running windows 2003 R2 (64 bit) - PostgreSQL 9.0.6 - 64bit. Today PostgreSQL has alot pgstat wait timeout in log. How to fix the PostgreSQL WARNING?? Tuan Hoang ANh
Re: [GENERAL] PGbouncer for Windows 2008
http://winpg.jp/~saito/pgbouncer/ On Fri, Jan 20, 2012 at 11:01 PM, Edison So edison@gmail.com wrote: Hello, Can anyone please tell me where I can find the PGbouncer executable and tutorial for Windows 2008? I found one but need to recompile on Windows. I also need intuitive instructions to configure it too. Thanks in advance. -- Edison
Re: [GENERAL] PostgreSQL recovery when lost some file in data\global
I can start postgresql but there is error 2011-09-28 15:09:37 ICT LOG: database system was interrupted; last known up at 2011-09-26 08:03:39 ICT 2011-09-28 15:09:37 ICT LOG: database system was not properly shut down; automatic recovery in progress 2011-09-28 15:09:37 ICT LOG: consistent recovery state reached at 0/7652700 2011-09-28 15:09:37 ICT LOG: record with zero length at 0/7652700 2011-09-28 15:09:37 ICT LOG: redo is not required 2011-09-28 15:09:37 ICT LOG: database system is ready to accept connections 2011-09-28 15:09:38 ICT LOG: autovacuum launcher started 2011-09-28 15:09:38 ICT ERROR: could not open file global/11867: No such file or directory 2011-09-28 15:09:39 ICT ERROR: could not open file global/11867: No such file or directory 2011-09-28 15:09:40 ICT ERROR: could not open file global/11867: No such file or directory 2011-09-28 15:09:41 ICT ERROR: could not open file global/11867: No such file or directory 2011-09-28 15:09:42 ICT ERROR: could not open file global/11867: No such file or directory 2011-09-28 15:09:43 ICT ERROR: could not open file global/11867: No such file or directory 2011-09-28 15:09:44 ICT ERROR: could not open file global/11867: No such file or directory 2011-09-28 15:09:45 ICT ERROR: could not open file global/11867: No such file or directory 2011-09-28 15:09:46 ICT ERROR: could not open file global/11867: No such file or directory Maybe all file in base directory are ok, postgresql only lost some file in global. Please help me. Sorry for my English Tuan Hoang Anh On Tue, Sep 27, 2011 at 4:55 PM, Venkat Balaji venkat.bal...@verse.inwrote: Hi Tuan Hoang Anh, Are you able to bring up the cluster ?? Please let us know what problem you are facing. Thanks Venkat On Tue, Sep 27, 2011 at 12:08 PM, tuanhoanganh hatua...@gmail.com wrote: I am running PostgreSQL 9.0.1 32bit on windows 2003. Last night my disk had some problem and i lost some file in data\global. Is there anyway to recovery postgresql. Thanks in advance. Sorry for my English. Tuan Hoang Anh
[GENERAL] PostgreSQL recovery when lost some file in data\global
I am running PostgreSQL 9.0.1 32bit on windows 2003. Last night my disk had some problem and i lost some file in data\global. Is there anyway to recovery postgresql. Thanks in advance. Sorry for my English. Tuan Hoang Anh
Re: [GENERAL] Links to Replication
You can use SymmetricDS to replicate on PostgreSQL, Oracle, SQL Server database. On Thu, Aug 25, 2011 at 11:07 AM, Shoaib Mir shoaib...@gmail.com wrote: On Thu, Aug 25, 2011 at 2:03 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, I am using PostgresPlus-8.4SS version of Postgres on Linux Windows Systems. Now I need to enable replication of two servers. OS may be same or different. Please let me know any useful links to do that. I am not really sure what PostgresPlus-8.4SS is? but if you were using PostgreSQL 8.4 then Slony (http://slony.info/) can be a good option, otherwise upgrade to 9.0 and go with streaming replication which out-of-box. cheers, Shoaib
[GENERAL] PostgreSQL 9.01 error database help
I am running PostgreSQL 9.01 on windows 2008, RAID 10 with 4 disk. Yesterday, one of 4 disks RAID 10 error and I copy data directory to USB. Today, When I start postgresql i have error log 2011-05-24 17:20:01 ICT LOG: database system was shut down at 2011-05-24 02:40:49 ICT 2011-05-24 17:20:01 ICT LOG: unexpected pageaddr 1/A1F8E000 in log file 1, segment 177, offset 16310272 2011-05-24 17:20:01 ICT LOG: invalid primary checkpoint record 2011-05-24 17:20:01 ICT LOG: invalid secondary checkpoint record 2011-05-24 17:20:01 ICT PANIC: could not locate a valid checkpoint record This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2011-05-24 17:20:01 ICT LOG: startup process (PID 6252) exited with exit code 3 2011-05-24 17:20:01 ICT LOG: aborting startup due to startup process failure Is there any way to help me? Thanks you very much Tuan Hoang Anh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pgdump error invalid page header in block
Yesterday, I had some problem with postgresql 9.0.2. Today i backup postgres and has error pg_dump: reading dependency data pg_dump: SQL command failed pg_dump: Error message from server: ERROR: invalid page header in block 299 of relation pg_depend_depender_index pg_dump: The command was: SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 pg_dump: *** aborted because of error Is there any way to fix it. Thanks in advance Tuan Hoang ANh
[GENERAL] PostgreSQL 9.0 service error : The service did not respond to the start or control request in a timely fashion.
Yesterday, my postgresql 9.0 service run well with user postgres. But today when it start, it have error The service did not respond to the start or control request in a timely fashion. When I change my user start service to Local System Account and check Allow service to interact with desktop, postgresql service start well. I have install dotNet framework 3.5 SP1 (include dotNet framework 1 Sp 1) on windows 2003 How to fix error. Thanks in advance Tuan Hoang Anh
[GENERAL] Re: PostgreSQL 9.0 service error : The service did not respond to the start or control request in a timely fashion.
I have postgresql 8.3.9 install on my server, this service run with user postgres and it start well On Wed, Jan 26, 2011 at 3:58 PM, tuanhoanganh hatua...@gmail.com wrote: Yesterday, my postgresql 9.0 service run well with user postgres. But today when it start, it have error The service did not respond to the start or control request in a timely fashion. When I change my user start service to Local System Account and check Allow service to interact with desktop, postgresql service start well. I have install dotNet framework 3.5 SP1 (include dotNet framework 1 Sp 1) on windows 2003 How to fix error. Thanks in advance Tuan Hoang Anh
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
I have changed archive_command to archive_command = 'copy %p D:\\3SDATABACKUP\\PITR\\WAL\\%f' and it work again. But why old archive_command work from 01/01 to 05/01 archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f' Thank for your help Tuan Hoang ANh On Fri, Jan 21, 2011 at 8:58 PM, Christian Ullrich ch...@chrullrich.netwrote: * tuanhoanganh wrote: Here is procmon i thinks error [some procmon events] No, that is all OK. The event at 2:39:55.7588651 is where Postgres starts cmd.exe to perform the copy. The really interesting data would be from cmd.exe itself, which implements the copy command. Please send the events from cmd.exe for the ten seconds following that timestamp. If it is overly much, please send it to me directly. -- Christian
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
NO, D is local driver. I setup PITR from 01/01/2011. It work well from 01-05/01/2011 (D:/3SDATABACKUP/PITR/WAL has 00010004005D) but error from 06/01/2011 On Thu, Jan 20, 2011 at 3:56 AM, John R Pierce pie...@hogranch.com wrote: On 01/19/11 9:23 AM, tuanhoanganh wrote: 2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E [... lots more ...] is D: a network mapped drive? those mappings are per user session specific, and the database service will not see them as it runs in a service session, not on the desktop session use a UNC instead if you are going across the network, eg, \\server\share\path\filename -- 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 9.0.1 PITR can not copy WAL file
I download postgresql from Enterprise DB On Thu, Jan 20, 2011 at 6:06 AM, Christian Ullrich ch...@chrullrich.netwrote: * Magnus Hagander wrote: On Wed, Jan 19, 2011 at 19:20, Christian Ullrichch...@chrullrich.net wrote: So when PostgreSQL runs copy 000...5E D:\..., it fails, and when you do the same thing as the PostgreSQL user, it works. Interesting. Try increasing the log level in postgresql.conf to see if it logs the error message from copy, or try xcopy instead of copy. Note thatn when PostgreSQL runs, it will shed any rights given through Administrators or Power Users group. So this is not an identical test. We cannot assume that the one-click installer was used, but if it was, the service account it creates will be a member of the Users group only. tuanhoanganh, what did you download to install Postgres? -- Christian
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
I have checked your solution. - Target disk full : No - PostgreSQL user does not have write privilege for the target directory : No - Target file exists already (then you have a bigger problem) : Last file in D:/3SDATABACKUP/PITR/WAL is 00010004005D - PostgreSQL user does not have full control privileges for the source file (the copy command needs them) : i switch to user postgres an copy 00010004005E from source to d:\temp and create new text file on D:/3SDATABACKUP/PITR/WAL it is ok. No access denied On Wed, Jan 19, 2011 at 6:56 PM, Christian Ullrich ch...@chrullrich.netwrote: * tuanhoanganh wrote: My PITR work well from 01/01/2011 to 06/01/2011. At 06/01/2011 postgresql log have issue 2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E [... lots more ...] 2011-01-06 08:28:58 ICT WARNING: transaction log file 00010004005E could not be archived: too many failures And my pg_xlog can not copy to D:/3SDATABACKUP/PITR/WAL from 06/01/2011. How to fix error ? Please help me Some possible reasons: - Target disk full - PostgreSQL user does not have write privilege for the target directory - Target file exists already (then you have a bigger problem) - PostgreSQL user does not have full control privileges for the source file (the copy command needs them) If you can stop your server, do so, then try to copy the file yourself on the command line. If that fails as well, you will get a better error message. If the server must stay up, copy the file to somwhere else on D: (assuming you don't have a volume mounted somewhere in the path). If you can copy the file yourself, you have a permissions problem. Make sure the PostgreSQL service user has full control on both the source and target directories. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
My PITR work well from 01/01/2011 to 06/01/2011. At 06/01/2011 postgresql log have issue 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_19.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_49.tmpct70s in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_34.kmtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_34.dmgiabancttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_34.dmckcttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_58.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_53.dmgiabancttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_120.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_119.dmgiabancttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_119.dmckcttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_119.kmtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_22.dmgiabancttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_18.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_15.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:27:55 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:55 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:27:56 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:56 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:27:56 ICT WARNING: transaction log file 00010004005E could not be archived: too many failures ... 2011-01-06 08:28:56 ICT LOG: archive command failed with exit code 1 2011-01-06 08:28:56 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:28:57 ICT LOG: archive command failed with exit code 1 2011-01-06 08:28:57 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:28:58 ICT LOG: archive command failed with exit code 1 2011-01-06 08:28:58 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:28:58 ICT WARNING: transaction log file 00010004005E could not be archived: too many failures And my pg_xlog can not copy to D:/3SDATABACKUP/PITR/WAL from 06/01/2011. How to fix error ? Please help me Here is my Archive # - Archiving - archive_mode = on# allows archiving to be done # (change requires restart) archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f'# command to use to archive a logfile segment Thanks in advance Tuan Hoang ANh
[GENERAL] PlPerl ODBC connect error question?
Can PLPerl make ODBC connect.
I have perl test connect. It work well
use DBI;
my $h = DBI-connect(
'dbi:ODBC:DRIVER=SQL
Server;Server=127.0.0.1;Database=TEST;Uid=sa;Pwd=abc123ABC;Port=1433',
'sa',
'abc123ABC',
{ AutoCommit = 1, RaiseError = 1, }
) or die Did not connect to db.;
print join(',', $h-tables);
But in PLPerlu it has error
CREATE OR REPLACE FUNCTION test_connection()
RETURNS void AS
$BODY$
use DBI;
my $dbh = DBI-connect(
'dbi:ODBC:DRIVER=SQL
Server;Server=127.0.0.1;Database=TEST;Uid=sa;Pwd=abc123ABC;Port=1433',
'sa',
'abc123ABC',
{ AutoCommit = 1, RaiseError = 1, }
);
if ($DBI::errstr) {
die ERR;
Could not connect to database
$DBI::errstr
ERR
}
RETURN;
$BODY$
LANGUAGE plperlu VOLATILE
COST 100;
ALTER FUNCTION test_connection() OWNER TO postgres;
AND when i execute function
SELECT test_connection();
Here is error
ERROR: Could not connect to database
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to
'TEST'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to
us_english. (SQL-01000) [state was 01000 now 01S00]
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
(SQL-01S00)
SQL state: XX000
Context: PL/Perl function test_connection
Re: [GENERAL] How to build plperl with PostgreSQL 9 on Windows
I have error when build plperl
1-- Build started: Project: plperl, Configuration: Release Win32 --
1Generate DEF file
1Not re-generating PLPERL.DEF, file already exists.
1Linking...
1 Creating library Release\plperl\plperl.lib and object
Release\plperl\plperl.exp
1SPI.obj : error LNK2019: unresolved external symbol _Perl_Tstack_base_ptr
referenced in function _XS__spi_exec_query
1Util.obj : error LNK2001: unresolved external symbol _Perl_Tstack_base_ptr
1SPI.obj : error LNK2019: unresolved external symbol
_Perl_Tmarkstack_ptr_ptr referenced in function _XS__spi_exec_query
1Util.obj : error LNK2001: unresolved external symbol
_Perl_Tmarkstack_ptr_ptr
1SPI.obj : error LNK2019: unresolved external symbol _Perl_Tstack_sp_ptr
referenced in function _XS__spi_exec_query
1Util.obj : error LNK2001: unresolved external symbol _Perl_Tstack_sp_ptr
1Util.obj : error LNK2019: unresolved external symbol _Perl_Tcurpad_ptr
referenced in function _XS___aliased_constants
1Util.obj : error LNK2019: unresolved external symbol _Perl_Top_ptr
referenced in function _XS___aliased_constants
1.\Release\plperl\plperl.dll : fatal error LNK1120: 5 unresolved externals
My ActivePerl version :This is perl, v5.10.1 built for
MSWin32-x86-multi-thread
How to fix it?
Please help me. Thanks in advance
Tuan Hoang ANh
On Wed, Jan 5, 2011 at 10:32 AM, tuanhoanganh hatua...@gmail.com wrote:
Thanks you very much
Here is my config.pl
$config-{perl} = c:\\perl;
1;
Strawberry Perl don't work because It don't have perl lib in
c:\Perl\lib\CORE\.
I swiched to ActivePerl to build plperl.Bu
Tuan Hoang ANh
On Wed, Jan 5, 2011 at 9:52 AM, Alex Hunsaker bada...@gmail.com wrote:
On Tue, Jan 4, 2011 at 19:27, tuanhoanganh hatua...@gmail.com wrote:
Thanks for answer me but it have error
Detected Visual Studio version 8.00
Detected hardware platform: Win32
Generating win32ver.rc for src\backend
Building src\pl\plperl\SPI.c...
'1' is not recognized as an internal or external command,
Instead of 1, try the path to the perl directory (such that
$config-{'perl'} . \bin\perl is the perl binary). For example:
$config-{perl} = C:\strawberry\perl\;
[GENERAL] How to build plperl with PostgreSQL 9 on Windows
I has set config.pl to
$config-{perl};
1;
and run build.bat, everything is ok, there is plpgsql.dll but don't found
plperl.dll.
I use strawberry perl 5.10 or must I use ActiveState Perl.
Please help me. Thank in advance
Tuan Hoang ANh
Re: [GENERAL] How to build plperl with PostgreSQL 9 on Windows
Thanks for answer me but it have error
Detected Visual Studio version 8.00
Detected hardware platform: Win32
Generating win32ver.rc for src\backend
Building src\pl\plperl\SPI.c...
'1' is not recognized as an internal or external command,
operable program or batch file.
Failed to create SPI.c.
Please help me.
Tuan Hoang ANh
On Wed, Jan 5, 2011 at 9:14 AM, Alex Hunsaker bada...@gmail.com wrote:
On Tue, Jan 4, 2011 at 10:12, tuanhoanganh hatua...@gmail.com wrote:
I has set config.pl to
$config-{perl};
1;
I don't know anything about the windows build system, but shouldn't
that be something like:
$config-{perl} = 1; ?
Re: [GENERAL] How to build plperl with PostgreSQL 9 on Windows
Thanks you very much
Here is my config.pl
$config-{perl} = c:\\perl;
1;
Strawberry Perl don't work because It don't have perl lib in
c:\Perl\lib\CORE\.
I swiched to ActivePerl to build plperl.
Tuan Hoang ANh
On Wed, Jan 5, 2011 at 9:52 AM, Alex Hunsaker bada...@gmail.com wrote:
On Tue, Jan 4, 2011 at 19:27, tuanhoanganh hatua...@gmail.com wrote:
Thanks for answer me but it have error
Detected Visual Studio version 8.00
Detected hardware platform: Win32
Generating win32ver.rc for src\backend
Building src\pl\plperl\SPI.c...
'1' is not recognized as an internal or external command,
Instead of 1, try the path to the perl directory (such that
$config-{'perl'} . \bin\perl is the perl binary). For example:
$config-{perl} = C:\strawberry\perl\;
[GENERAL] Stream Replication archive command Access is denied question
I setup my PostgreSQL 9.0.1 Stream Replication. Here is my config of master database address 192.168.5.4 pg_hba.conf # IPv4 local connections: hostall all 0.0.0.0/0trust # IPv6 local connections: hostall all ::1/128 trust host replication postgres 0.0.0.0/0 trust postgresql.conf wal_level = hot_standby archive_mode = on archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f'# command to use to archive a logfile segment max_wal_senders = 5 wal_keep_segments = 32 Here is my config of standby database postgresql.conf hot_standby = on recovery.conf standby_mode = 'on' primary_conninfo = 'host=192.168.5.4 port=5432 user=postgres' trigger_file = 'standby.stop' restore_command = 'copy \\192.168.5.4\pitr\wal\%f %p' Stream Replication work well, every thing I update on master database will exist on standby database. But on master database log have error 2011-01-01 10:14:19 ICT LOG: archive command failed with exit code 1 2011-01-01 10:14:19 ICT DETAIL: The failed archive command was: copy pg_xlog\000100030056 D:\3SDATABACKUP\PITR\WAL\000100030056 Access is denied. 2011-01-01 10:14:20 ICT LOG: archive command failed with exit code 1 2011-01-01 10:14:20 ICT DETAIL: The failed archive command was: copy pg_xlog\000100030056 D:\3SDATABACKUP\PITR\WAL\000100030056 Access is denied. 2011-01-01 10:14:21 ICT LOG: archive command failed with exit code 1 2011-01-01 10:14:21 ICT DETAIL: The failed archive command was: copy pg_xlog\000100030056 D:\3SDATABACKUP\PITR\WAL\000100030056 2011-01-01 10:14:21 ICT WARNING: transaction log file 000100030056 could not be archived: too many failures On computer of master database (192.168.5.4), PITR directory only have D:/3SDATABACKUP/PITR/WAL/000100030053 D:/3SDATABACKUP/PITR/WAL/000100030053.0020.backup D:/3SDATABACKUP/PITR/WAL/000100030054 D:/3SDATABACKUP/PITR/WAL/000100030055 D:/3SDATABACKUP/PITR/WAL/000100030055.0020.backup Please Help me how to fix error on master database and will restore_command on standby database work? Thanks in advance. Tuan Hoang ANh
Re: [GENERAL] Stream Replication archive command Access is denied question
Is by any chance D: a network mapped drive in Windows ? No, D is a driver on master computer. I can copy 000100030056 file from postgresql data\pg_xlog to D:/3SDATABACKUP/PITR On Sat, Jan 1, 2011 at 4:30 PM, John R Pierce pie...@hogranch.com wrote: On 01/01/11 1:23 AM, tuanhoanganh wrote: archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f'# command to use to archive a logfile segment ... 2011-01-01 10:14:19 ICT LOG: archive command failed with exit code 1 2011-01-01 10:14:19 ICT DETAIL: The failed archive command was: copy pg_xlog\000100030056 D:\3SDATABACKUP\PITR\WAL\000100030056 Access is denied. Is by any chance D: a network mapped drive in Windows ? drive mappings are by user session. the postgres service is running in a different user session than your interactive desktop session, so your desktop shared drives mean nothing to it. if you're copying across the network, use the UNC name, like archive_command = 'copy %p //servername/sharename/3SDATABACKUP/PITR/WAL/%f'# command to use to archive a logfile segment and make sure that on the server, there is a postgres user with the same password as the service account. or, if you are in an active directory environment, use a domain account to run the service. -- 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] Stream Replication archive command Access is denied question
Thanks in advance. When I share D:/3SDATABACKUP/PITR on master computer, I set account postgres only read when access \\192.168.5.4\pitr but directory sercurity of D:/3SDATABACKUP/PITR user postgers has been changed to read only. I had changed directory sercurity of D:/3SDATABACKUP/PITR user postgers to full access. Everything work fine. Thanks you very much. Sorry for my English. Tuan Hoang ANh. On Sat, Jan 1, 2011 at 5:18 PM, John R Pierce pie...@hogranch.com wrote: On 01/01/11 1:50 AM, tuanhoanganh wrote: Is by any chance D: a network mapped drive in Windows ? No, D is a driver on master computer. I can copy 000100030056 file from postgresql data\pg_xlog to D:/3SDATABACKUP/PITR does the postgres service account have write access to that directory ? check the access control lists on the directory and parents. note that postgres might NOT be a member of 'Users' or other generic groups -- 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 restore from backup to 8.4.3 server using 9.0 dump/restore
You can test restore by change CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql; to CREATE PROCEDURAL LANGUAGE plpgsql; Tuan Hoang Anh 2010/12/15 Andrus Moor eetas...@online.ee Server is PostgreSQL 8.4.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 32-bit Backup is created using 9.0RC pg_dump.exe file Trying to restore from this backup to same server using 9.0RC pg_restore.exe causes error ..\pg_dump\pg_restore.exe -h mysite.com -U eur1_owner -i --no-privileges --no-owner -d eur1 C:\mybackup.backup pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 929; 2612 16389 PROCEDURAL LANGUAGE plpgsql postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near PROCEDURAL LINE 1: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql; How to restore from this backup to 8.4.3 server using 9.0 pg_restore ? Andrus. -- 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] Bytea error in PostgreSQL 9.0
Thanks for your help. Is there any .Net or VB tutorial new 9.0 bytea? Tuan Hoang Anh On Tue, Dec 14, 2010 at 1:27 PM, Mark Felder f...@feld.me wrote: On Mon, 13 Dec 2010 23:06:32 -0600, tuanhoanganh hatua...@gmail.com wrote: I have program work with bytea, this field store image. Program work well in postgresql 8.3.9 but error in postgresql 9.0 I don't know if this is your problem, but bytea changed in Postgres 9.0. Could you try enabling set bytea_output = escape? Regards, Mark -- 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] Bytea error in PostgreSQL 9.0
Thanks for your answer. But I don't want to change db config or ALTER DATABASE data SET bytea_output='escape'; I can change my app. So is there document introduce how to work with postgresql 9 bytea ( I don't want to user lo_create, lo_import. lo_export) Tuan Hoang Anh On Tue, Dec 14, 2010 at 8:55 PM, Ivan Voras ivo...@freebsd.org wrote: On 14/12/2010 14:51, tuanhoanganh wrote: Thanks for your help. Is there any .Net or VB tutorial new 9.0 bytea? You do not need to change your code if you add bytea_output = 'escape' # hex, escape into postgresql.conf. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Bytea error in PostgreSQL 9.0
I have program work with bytea, this field store image. Program work well in postgresql 8.3.9 but error in postgresql 9.0 Here is code to write image to database FileStream srcStream = new FileStream(file_name, FileMode.Open, FileAccess.Read); byte[] arrImage = new byte[srcStream.Length]; int read = srcStream.Read(arrImage, 0, arrImage.Length); string sql = INSERT INTO hrnvpict(ma_nv,pict) VALUES(@ma_nhan_vien ,@arrImage); Npgsql.NpgsqlConnection c = Public.cn; Npgsql.NpgsqlCommand comm = new NpgsqlCommand(sql, c); comm.Parameters.Add(new NpgsqlParameter(@arrImage, DbType.Binary)).Value = arrImage; comm.Parameters.Add(new NpgsqlParameter(@ma_nhan_vien, DbType.String, 40)).Value = _ma_nv; comm.ExecuteNonQuery(); And Here is code to read image from database string cmd = select pict from hrnvpict where trim(ma_nv)= ' + _ma_nv + '; Npgsql.NpgsqlConnection c = Public.cn; Npgsql.NpgsqlCommand comm = new NpgsqlCommand(cmd, c); Byte[] result = (Byte[])comm.ExecuteScalar(); MemoryStream pic = new MemoryStream(result); pictureBox1.Image = Image.FromStream(pic); //- 9.0 error here parameter is not valid My postgresql 8.3 install is made by msi download from www.postgresql.org. Postgresql 9 install is made by EnterpriseDB, it has LC_COLLATE = 'English_United States.1252' and LC_CTYPE = 'English_United States.1252' (In 8.3 I cannot found this) How to fix this. Please help me. Sorry for my English. Tuan Hoang Anh
[GENERAL] Bytea error in PostgreSQL 9.0
I have program work with bytea, this field store image. Program work well in postgresql 8.3.9 but error in postgresql 9.0 Here is code to write image to database FileStream srcStream = new FileStream(file_name, FileMode.Open, FileAccess.Read); byte[] arrImage = new byte[srcStream.Length]; int read = srcStream.Read(arrImage, 0, arrImage.Length); string sql = INSERT INTO hrnvpict(ma_nv,pict) VALUES(@ma_nhan_vien ,@arrImage); Npgsql.NpgsqlConnection c = Public.cn; Npgsql.NpgsqlCommand comm = new NpgsqlCommand(sql, c); comm.Parameters.Add(new NpgsqlParameter(@arrImage, DbType.Binary)).Value = arrImage; comm.Parameters.Add(new NpgsqlParameter(@ma_nhan_ vien, DbType.String, 40)).Value = _ma_nv; comm.ExecuteNonQuery(); And Here is code to read image from database string cmd = select pict from hrnvpict where trim(ma_nv)= ' + _ma_nv + '; Npgsql.NpgsqlConnection c = Public.cn; Npgsql.NpgsqlCommand comm = new NpgsqlCommand(cmd, c); Byte[] result = (Byte[])comm.ExecuteScalar(); MemoryStream pic = new MemoryStream(result); pictureBox1.Image = Image.FromStream(pic); //- 9.0 error here parameter is not valid My postgresql 8.3 install is made by msi download from www.postgresql.org. Postgresql 9 install is made by EnterpriseDB, it has LC_COLLATE = 'English_United States.1252' and LC_CTYPE = 'English_United States.1252' (In 8.3 I cannot found this) How to fix this. Please help me. Sorry for my English. Tuan Hoang Anh
Re: [GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
I have same question My Computer is running POS with Postgres 8.9.11 database Ram : 16GB OS : Windows 2008 R2 CPU XEON 2G User : 50-60 user (connect ~ 200 connects, I increase Windows SharedSection=1024,20480,1024 for 125 connects). DISK : RAID 1 What will be good settings for DB parameters such as shared_buffers, checkpoint_segment and etc. My application run slowly when = 30 users Thank for your help. Tuan Hoang Anh On Thu, Nov 11, 2010 at 2:59 PM, AI Rumman rumman...@gmail.com wrote: I am going to install Postgresql 9.0 for my running applicaiton which is at 8.1. My Db size is 3 GB. Server Specification: dual-core 4 cpu RAM: 32 GB OS: Centos What will be good settings for DB parameters such as shared_buffers, checkpoint_segment and etc. Any help please.
Re: [GENERAL] How to do hot backup using postgres
Can anyone answer me ?
Thanks you very much
Tuan Hoang Anh
On Sat, Aug 14, 2010 at 10:00 AM, tuanhoanganh hatua...@gmail.com wrote:
I tried to do pitr backup using Postgres 8.3.9 on windows. So I issued
SELECT pg_start_backup('test');
After I put the db in backup mode I tried to zip the data directory files
with 7z. However I encountered the following errors:
The process cannot access the file because it is being used by another
process.
C:\...\8.3\data\global1214
The process cannot access the file because it is being used by another
process.
C:\...\8.3\pg_xlog\00010007001B
...
...
But zip file was created.
Is it error ? How can I zip the files and perform a hot backup on windows?
[GENERAL] How to do hot backup using postgres
I tried to do pitr backup using Postgres 8.3.9 on windows. So I issued
SELECT pg_start_backup('test');
After I put the db in backup mode I tried to zip the data directory files
with 7z. However I encountered the following errors:
The process cannot access the file because it is being used by another
process.
C:\...\8.3\data\global1214
The process cannot access the file because it is being used by another
process.
C:\...\8.3\pg_xlog\00010007001B
...
...
But zip file was created.
Is it error ? How can I zip the files and perform a hot backup on windows?
Re: [GENERAL] pgtune
What is the name of DW in --type=DW Sorry for my English. Tuan Hoang Anh On Mon, Aug 9, 2010 at 6:21 PM, Amitabh Kant amitabhk...@gmail.com wrote: 2010/8/9 Sim Zacks s...@compulab.co.il I just found out about pgtune and am trying it out on my server. I have 2.5 questions: 1) Are these settings the maximum that the server will handle, if it is strictly dedicated to postgresql? Meaning if I am running other stuff on the server as well, this would be a bad idea. 1a) If I have some intense plpython stored procedures, do they run in the postgresql memory space (ie using the memory settings from the postgresql.conf, or do they run under their own memory space and then I would have to take that into account when allocating postgresql memory? 2) If it sets my max_connections to 80 and would like to set it at 300, what would be the appropriate setting to lower at its expense? Sim Look at the options available in pgtune -M TOTALMEMORY, --memory=TOTALMEMORY Total system memory, will attempt to detect if unspecified -T DBTYPE, --type=DBTYPE Database type, defaults to Mixed, valid options are DW, OLTP, Web, Mixed, Desktop -c CONNECTIONS, --connections=CONNECTIONS Maximum number of expected connections, default depends on database type For question 1, you can set the type of server you want. For question 2, you can pass the -c parameter and it would adjust the other parameters. Not sure of 1a though. Amitabh Kant
[GENERAL] Rescue data after power off
Is there anyway to rescue data afer power off. I have postgres database version 8.3.9 on windows 2003. Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. Please help me. I am very confuse. Tuan Hoang Anh
Re: [GENERAL] Rescue data after power off
Yes, I don't have backup. On Mon, Jul 19, 2010 at 4:24 PM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to tuanhoanganh : Is there anyway to rescue data afer power off. I have postgres database version 8.3.9 on windows 2003. Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. Restore the data from your backup. You haven't a backup? Your problem. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Rescue data after power off
When I use pgadmin to view data of table dmvt, it have data. But when I select * from dmvt there is error on log 2010-07-19 17:32:17 ICTWARNING: invalid page header in block 207 of relation dmvt; zeroing out page 2010-07-19 17:32:35 ICTLOG: server process (PID 3480) was terminated by exception 0xC005 2010-07-19 17:32:35 ICTHINT: See C include file ntstatus.h for a description of the hexadecimal value. 2010-07-19 17:32:35 ICTLOG: terminating any other active server processes 2010-07-19 17:32:35 ICTWARNING: terminating connection because of crash of another server process 2010-07-19 17:32:35 ICTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2010-07-19 17:32:35 ICTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-07-19 17:32:35 ICTWARNING: terminating connection because of crash of another server process 2010-07-19 17:32:35 ICTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2010-07-19 17:32:35 ICTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-07-19 17:32:36 ICTLOG: all server processes terminated; reinitializing 2010-07-19 17:32:46 ICTFATAL: pre-existing shared memory block is still in use 2010-07-19 17:32:46 ICTHINT: Check if there are any old server processes still running, and terminate them. Is there anyway to fix it. On Mon, Jul 19, 2010 at 5:00 PM, tuanhoanganh hatua...@gmail.com wrote: Yes, I don't have backup. On Mon, Jul 19, 2010 at 4:24 PM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to tuanhoanganh : Is there anyway to rescue data afer power off. I have postgres database version 8.3.9 on windows 2003. Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. Restore the data from your backup. You haven't a backup? Your problem. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Rescue data after power off
I have checked SHOW fsync, It is ON. When I view table dmvt on PgAdmin, it only has 1332 rows, but command SELECT count(*) FROM dmvt return 2449 rows. My postgresql.conf is default of EnterpriseDB Postgres 8.3.9. Please help me. Sorry for my English. Tuan Hoang Anh. On Mon, Jul 19, 2010 at 9:36 PM, Craig Ringer cr...@postnewspapers.com.auwrote: On 19/07/10 19:02, Jayadevan M wrote: Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. The Power Off - is it a planned/regular one? If yes, it makes sense to have a normal shutdown of the database before the Power Off. ... but even if you yank the power plug out of the back of the server, PostgreSQL should *NEVER* lose comitted data (unless you've told it its allowed to with a commit delay) and should certainly NEVER damage the database structure. It's one of PostgreSQL's most important and basic features. Unless you have set fsync=off in postgresl.conf, in which case the data loss is entirely your own fault, this should not happen. If you do not know if fsync is on or off, check by running SHOW fsync; in psql or PgAdmin. It should say on. If it says off then you or someone else manually told the database not to try to protect your data from power loss or other failures. If you have lost data and fsync is on, then most likely your RAID controller/disks are doing something they should not be like caching writes in volatile storage. Make sure your RAID controller has write caching disabled or has a tested and working battery backup unit. If in doubt, your raid controller should have its write cache turned off, ie it should be in write-through mode and NOT in write-back mode. -- Craig Ringer
Re: [GENERAL] Pg dump Error
On error computer i dump 2 databases. The first dump well but the second dump fail. My Postgres server client version are 8.3.3-1 I call it by command D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h192.168.0.1 -U postgres -W -p 5433 -Fp -C -fsys.out sys -- OK D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h192.168.0.1 -Upostgres -W -p5433 -Fp -C -fmyacc.out myacc -- have error I test with dump on PgAdmin. It have error too. Please help me. Thank you. Sorry for my english. On Wed, Aug 20, 2008 at 2:40 AM, Alvaro Herrera [EMAIL PROTECTED]wrote: tuanhoanganh escribió: I have a problem with pg_dump on 2 computers. On the first computer, pg_dump runs very well, but on the second pg_dump has a error : pg_dump: reading triggers for table ph57 pg_dump: reading triggers for table phts pg_dump: reading dependency data pg_dump: SQL command failed pg_dump: Error message from server: message contents do not agree with length in message type D message contents do not agree with length in message type c server sent data (D message) without prior row description (T message) pg_dump: The command was: SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 pg_dump: *** aborted because of error What Postgres version is the server, what's the pg_dump version on each of these computers, and how are you invoking pg_dump? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
[GENERAL] Pg dump Error
I have a problem with pg_dump on 2 computers. On the first computer, pg_dump runs very well, but on the second pg_dump has a error : pg_dump: reading triggers for table ph57 pg_dump: reading triggers for table phts pg_dump: reading dependency data pg_dump: SQL command failed pg_dump: Error message from server: message contents do not agree with length in message type D message contents do not agree with length in message type c server sent data (D message) without prior row description (T message) pg_dump: The command was: SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 pg_dump: *** aborted because of error Please help me. Thank you very much. Sorry for my English TuanHa
[GENERAL] array function problem
I have a array function
CREATE OR REPLACE FUNCTION temp.rowfromarray(text[])
RETURNS SETOF text AS
$BODY$DECLARE
_returntext;
BEGIN
for i in 1..array_upper($1,1) loop
_return := $1[i];
return next _return;
end loop;
return;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION temp.rowfromarray(text[]) OWNER TO postgres;
I call it by command
select temp.rowfromarray(string_to_array('1,2,3,4,5', ','));
but it have error
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function rowfromarray line 6 at RETURN NEXT
** Error **
ERROR: set-valued function called in context that cannot accept a set
SQL state: 0A000
Context: PL/pgSQL function rowfromarray line 6 at RETURN NEXT
When i debug it by pass {1,2,3,4,5} to first parameter, it run well.
Please help me.
Thanks you very much. Sorry for my English.
[GENERAL] Function parameters change when update to 8.3
I have function in PostgreSQL 8.2 CREATE OR REPLACE FUNCTION post_ctpxd(ud_stt_rec) RETURNS void AS $BODY$DECLARE ... BEGIN END $BODY$ LANGUAGE 'plpgsql' VOLATILE; My domain in 8.2 CREATE DOMAIN ud_stt_rec AS character(20) DEFAULT ''::bpchar NOT NULL; But when I dump database and restore to 8.3 this function change to CREATE OR REPLACE FUNCTION post_ctpt1(ud_stt_rec(24)) RETURNS void AS $BODY$DECLARE ... BEGIN ... END $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; My domain in 8.3 CREATE DOMAIN ud_stt_rec AS character(20) DEFAULT ''::bpchar; So when I run script CREATE OR REPLACE FUNCTION post_ctpt1 it have error ERROR: type modifier is not allowed for type ud_stt_rec ** Error ** ERROR: type modifier is not allowed for type ud_stt_rec SQL state: 42601 Please help me. Thank you very much. Tuan Hoang Anh [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function parameters change when update to 8.3
On 2/22/08, Tom Lane [EMAIL PROTECTED] wrote: tuanhoanganh [EMAIL PROTECTED] writes: ... But when I dump database and restore to 8.3 this function change to CREATE OR REPLACE FUNCTION post_ctpt1(ud_stt_rec(24)) It works as expected for me. Please provide a *complete* test case, including the specific dump and reload procedures you are using. regards, tom lane My ud_stt_rec domain CREATE DOMAIN ud_stt_rec AS character(20) DEFAULT ''::bpchar; ALTER DOMAIN ud_stt_rec OWNER TO postgres; I write script in Query of pgAdminIII CREATE OR REPLACE FUNCTION test(ud_stt_rec) RETURNS void AS $BODY$ BEGIN RETURN; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION test(ud_stt_rec) OWNER TO postgres; This script run well. In functions, I have a new function test. But when I choose CREATE script with function test the script change to -- Function: test(ud_stt_rec(24)) -- DROP FUNCTION test(ud_stt_rec(24)); CREATE OR REPLACE FUNCTION test(ud_stt_rec(24)) RETURNS void AS $BODY$ BEGIN RETURN; END $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION test(ud_stt_rec(24)) OWNER TO postgres; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
