Re: [GENERAL] Restore problem
On Wed, December 29, 2010 10:59, John R Pierce wrote: > On 12/29/10 4:34 AM, Leif Biberg Kristensen wrote: >> Back when I used Windows, my favorite editor was EditPlus >> (http://www.editplus.com/). It isn't free, but well worth the 35 bucks. > > other good choices are Notepad++ (free) and my personal favorite, > UltraEdit ($$). > > UEdit has some nice stuff like being able to load/save directly from > FTP, unix2dos/dos2unix built in (and it is perfectly happy editing > native unix format files), rather powerful macros, column select, etc. > > I'd also like to throw in Context for Windows as an Editor. It's also free and has syntax highlighting for almost everything imaginable (on Windows and *ix). -- Timothy J. Bruce -- 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 know the password for the user 'postgres'
On Tue, October 28, 2008 05:57, Tom Lane wrote: > Sam Mason <[EMAIL PROTECTED]> writes: >> On Tue, Oct 28, 2008 at 10:42:47AM +0100, Thomas wrote: >>> An easy trick I have found to set postgres password: $ sudo passwd >>> postgres, and now you can type a new password. So now you can switch >>> user with: $ su postgres, and then connect to the DB with psql. > >> Won't that allow logins to the postgres account then? > > True, but that might be safer overall than giving out sudo privileges. > If the sysadmin and the DBA are the same person it hardly matters, > but if you want the DBA to not have root, then giving him a password for > the postgres account is the best way. So it all depends on your > local situation ... > > regards, tom lane > Wouldn't it be better to add the line 'sudo su - postgres' as the entry (command) for the user(s) in the sudoers file? This would specifically limit the user(s) to only being able to change to the postgres user's context. I think this goes to overall system security, just like the security methods wrapped around PostgreSQL itself. Weakening system security is no different than weakening access to the database. Tim -- Timothy J. Bruce Registered Linux User #325725 -- 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] Looking for advice on database encryption
On Thu, April 16, 2009 13:20, Bill Moran wrote: > In response to Thomas Kellerer : > >> Bill Moran wrote on 16.04.2009 21:40: >> > The goal here is that if we're going to encrypt the data, it should >> > be encrypted in such a way that if an attacker gets ahold of a dump >> > of the database, they still can't access the data without the >> > passphrases of the individuals who entered the data. >> >> I'm by far not an expert, but my naive attempt would be to store the the >> database files in an encrypted filesystem. > > That was the first suggestion when we started brainstorming ideas. > Unfortunately, it fails to protect us from the most likely attack > vector: SQL Injection/application layer bugs. In an SQL Injection > (for example) the fact that the filesystem is encrypted does zero > to protect the sensitive data. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > I'll chime in here, even though I probably shouldn't. A lot is dependent on what standard you're trying to meet. General Security (and Common Sense) vs PCI/DSS vs NSA/DoD vs some other standard. Do you need to decrypt the values once they're in the system? Do you need the items in an index? Do the values need to be part of a constraint / foreign key relationship (because a hashed value may cause you a lot of headaches!)? Look at these different scenarios and think about the data (both in encrypted format and unencrypted format) before you decide HOW you want to do it. Tim -- Timothy J. Bruce -- 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 speed up the first-time-searching in pgsql?
On Thu, May 28, 2009 01:23, Richard Huxton wrote: > zxo102 ouyang wrote: >> Hi there, >> Thanks for your suggestions. I do have an application running on the >> machine >> all the time. In fact, the application keeps writing real-time >> monitoring >> data into the database. Based on my understanding of your messages, I >> can't >> do anything to speed up the first-time-searching. Probably I can give a >> waiting process bar to the users and let them wait for the results. > > No, I think you missed the detail on some of the answers. There are > limitations (as discussed in the answers), but the simple suggestion in > the first answer will probably help a lot. > > Set up a scheduled task to run a big search of the database an hour > before people start work. This can be as simple as a .BAT file running > "SELECT * FROM big_table" triggered by Windows' Task Scheduler. > > -- >Richard Huxton >Archonet Ltd > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > As a point of note - if you're running Windows XP / Windows NT or higher (i.e., not Win 9x) you should use .CMD files instead of traditional .BAT files. While they accomplish the same thing, the .BAT files run in a "shared" 16-bit environment as opposed to the 32-bit (or 64-bit) memory space. Any application that crashes in the 16-bit environment will crash or cause instability in other applications running in the same 16-bit environment (since it's shared). Functionally, there is no difference (other than expanded functions) in the .CMD scripts. All .BAT commands work the same in .CMD - you just need to rename your .BAT files to .CMD files. Tim -- Timothy J. Bruce -- 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] High I/O writes activity on disks causing images on browser to lag and not load
On Wed, June 3, 2009 12:59, Bill Moran wrote: > In response to Jennifer Trey : > >> Hmm, I just noticed the same write behavior on my Windows Xp laptop but >> the >> values was a little less. >> I even created an DB with one table and column and this still happened >> when querying it. > > By "created", you mean you created a table and populated it with data? > Once you do that, do a "SELECT count(*)" on that table, then wait for > the I/O to calm down. That select statement will force all the hint > bits to be updated. See if subsequent selects still cause disk > activity. > >> Are you sure that moving to Linux will solve this? > > I never advocated that Linux would fix this, and I still don't. I > recommended a short list of methods to investigate the issue, most of > which you ignored. You _still_ don't know what's being written, and > I _highly_ recommend that you isolate that before doing something > radical like switching operating systems. > > If you've got the DB configured in such a way that it's causing a lot of > write ops, it's going to do it in Linux or any other Posix systems, or > on CP/M for that matter. > > Posix systems have a laundry list of tools to identify what programs are > doing. It's been a while since I've worked with Windows, but I seem to > remember MS having tools to audit disk activity. Turn them on and see > which files are actually being written to. > >> Could you please check if >> you notice the same write behavior? > > My BSD-based systems to no do this. Doing a select count(*) on a table > with 750,000 rows produces no write activity. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Jennifer, While not the "Best" solution by any stretch, you can use Windows Task Manager (instead of Windows Performance Manager) to track the I/O writes for each process. Start Task Manager (run %windir%\system32\tskmgr32.exe) On the Process Tab check the box "Show processes from all users" Select View->Select Columns Check the boxes related to I/O that you want to examine. Sort the processes by "Image Name" to group PostgreSQL related processes (again on the Process tab). Watch your I/O counters that you selected. Run your query (SQL Query) again. This is to determine if it is PostgreSQL doing the disk I/O or Windows. Again - this is a rough guesstimate. Better data can be collected by using the SysInternals tools (available for free from the Microsoft website) or using the performance tools in Windows to watch the "real" disk I/O parameters. Good Luck. Tim -- Timothy J. Bruce Registered Linux User #325725 kboodu -- 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] High I/O writes activity on disks causing images on browser to lag and not load
On Wed, June 3, 2009 13:44, Jennifer Trey wrote: > On Wed, Jun 3, 2009 at 10:59 PM, Bill Moran > wrote: > >> In response to Jennifer Trey : >> >> > Hmm, I just noticed the same write behavior on my Windows Xp laptop >> but >> the >> > values was a little less. >> > I even created an DB with one table and column and this still happened >> > when querying it. >> >> By "created", you mean you created a table and populated it with data? >> Once you do that, do a "SELECT count(*)" on that table, then wait for >> the I/O to calm down. That select statement will force all the hint >> bits to be updated. See if subsequent selects still cause disk >> activity. >> > > No, I created a new DB, created a table, and did not even populate any > data. > > Running select count(*) from test > > just now, still caused the 10-20 I/O-writes. > > >> >> > Are you sure that moving to Linux will solve this? >> >> I never advocated that Linux would fix this, and I still don't. I >> recommended a short list of methods to investigate the issue, most of >> which you ignored. You _still_ don't know what's being written, and >> I _highly_ recommend that you isolate that before doing something >> radical like switching operating systems. > > > I didn't ignore all of them. > When it comes to the logging I am still not sure. What file should I be > looking at ? The standard log file currently has 5 lines in it, and its > only > errors. > When it comes to things set as wrong, it might be true. However, on the > laptop I've only installed and ran Tuning Wizard and haven't touched it > afterwards. > > No, I still don't know whats being written. I have tried to isolate it, > and > checked several folders, but can't find the path. > > The statement i made earlier about how there was no reads was false. There > is reads and they are done mostly by another thread. I was checking the > same > process at that time. However, the combined sum of I/O shows that there > are > more writes than reads with postgresql. Currently on the server by 2.25 > > >> >> >> If you've got the DB configured in such a way that it's causing a lot of >> write ops, it's going to do it in Linux or any other Posix systems, or >> on CP/M for that matter. >> >> Posix systems have a laundry list of tools to identify what programs are >> doing. It's been a while since I've worked with Windows, but I seem to >> remember MS having tools to audit disk activity. Turn them on and see >> which files are actually being written to. >> > > I will try to find such a tool. > > >> >> > Could you please check if >> > you notice the same write behavior? >> >> My BSD-based systems to no do this. Doing a select count(*) on a table >> with 750,000 rows produces no write activity. >> > > Thats good to know. > > >> >> -- >> Bill Moran >> http://www.potentialtech.com >> http://people.collaborativefusion.com/~wmoran/ >> > > > Grzegorz, i have considered the hosting solutions. Problem is money. I am > still a student. I might take you up on the other offer though :) > > Scott, how much would such a controller cost me? > > Tim, yes, I am using the tool "ProcessExplorer" from the windows site. It > shows all the activity but can't see to where those writes are being done > with that tool. Any ideas? > > > Thanks all, appreciate all your help and effort. > > Sincerely / Jennifer > Jennifer, I don't think it will tell you which files are being written to by which process. That you'd need something else for and I don't know of any tools that tell me that. I know DiskMon (also from SysInternals) will watch your disk activity, but it doesn't show processes or filenames that I can find. Windows PerfMon will give you some detail. I get to it from a shortcut on my Administrative Tools menu item: %SystemRoot%\system32\perfmon.msc /s ). (I don't know why there's a /s on the command line.) You can add various performance counters to analyze the disk. But even that is for the disk...not by process or application. I'm only able to get a breakdown of processes doing disk i/o using Windows Task Manager (unless someone else knows of a solution). And I don't know of a way to capture and save that to make comparisons. Tim -- Timothy J. Bruce visit my Website at: http://www.tbruce.com Registered Linux User #325725 -- 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] interface for "non-SQL people"
On Fri, October 9, 2009 09:06, Andrew Gould wrote: > On Fri, Oct 9, 2009 at 10:15 AM, Joshua D. Drake > wrote: >> On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote: >>> On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure >>> wrote: >>> > >>> > The #1 tool you have at your disposal is the human brain. I >>> > personally think GUI database tools are counter productive and huge >>> > time wasters. SQL requires lateral thinking but once you have your >>> > head around how joins work and the general syntax of queries you >>> > should have no problem getting data out of your database. SQL is a >>> > 'man machine interface' :-). It's a very high level language with a >>> > lot of power. The gui 'wrappers' that I've seen actually obfuscate >>> > the concepts. >>> >>> Amen to that. I'd rather spend a little bit of my time each week >>> going over correlated subqueries with a user than trying to get good >>> performance on a reporting server that's hammered by bad queries. >>> Which is what a lot of query builders basically do. >> >> Good lord people. Not be helpful much? This guy is obviously not an SQL >> guy and nor should he have to be. My guess is he is an analyst sitting >> in a company somewhere that just wants to whip together some simple data >> entry reports or contact forms. >> >> There is *zero* reason he should have to dig around in the confounded >> tar pit of SQL. Not everyone can be an SQL guru. >> >> To answer the question. >> >> Open Office has a forms interface that works with PostgreSQL >> You can use MS Access >> You have use Kexi (KDE) >> >> There is also one written in GTK and Python but I don't recall the >> name... >> >> JD... Who sits in bewilderment >> > > My 2 cents: > > If the poster works in MS Windows, MS Access is the easiest, most > powerful and most flexible solution for a client application. I > use it for complex ad hoc projects as well as creating applications. > To keep maintenance simple, should I get hit by the proverbial bus, I > do not use any manually created code (SQL or VB) in my projects. I > use complex combinations of functions, but even those are typed within > the QBE (query by example) window. > > Much of MS Access's comparative value comes from the fact that it > treats local tables, select queries and links to external data sources > as if they were all local tables. This means that you can nest > queries easily or join local tables to external tables. You can even > join tables from different database servers. Documentation for MS > Access is plentiful. > > Keep in mind that MS Access does not share well and that the files > become corrupt/unusable when they reach 2GB (less admin resources for > tables, queries, etc) in size. > > The down-side of GUI clients and ODBC connections is that you will not > be able to use features that are specific to the database server. > > Most GUI database front-ends available for unix operating systems > focus on database administration as opposed to serious data analysis. > I tried Kexi and OpenOffice Base briefly, but became frustrated trying > to work with both local and remote data within the same project. > > Gnuplot and R provide graphical views of data; but you still have to > write code (not SQL) manually. You also have to export data from the > database server in order to feed the data to these applications. > > My rule of thumb for analysis is this: If I'm working in Windows PC, > I use MS Access. If I'm working in *BSD or Linux, I store SQL code in > text files and am careful to use a naming scheme that facilitates > management of those files. > > I have tried using Windows and MS Access within virtual machines. It > works for small and simple queries. If you're doing large jobs or > complex analysis, however, you may have issues with memory management. > > Here's some PostgreSQL-specific information: > > If you are using MS Access as a client to a PostgreSQL server via > ODBC, you should keep the following in mind: > 1. In the ODBC data source configuration, use true = -1. > 2. In the database server, null values should not be allowed > for boolean (true/false, yes/no) data fields. > > Best of luck, > > Andrew > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > I would also add that there are a LOT of performance issues with MS Access. For one, in some cases filtering out records with a where clause can be performed on the client side - meaning it grabs all records and pulls them across the network and then filters out the results only displyaing the results you want to see. I've seen this happen a lot at my company, but it may be related to the type of queries being run, so you may experience different results. Tim -- Timothy J. Bruce Registered Linux User #325725 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
On Tue, February 2, 2010 08:23, Alvaro Herrera wrote:
> dipti shah escribió:
>> Techdb=# show hba_file;
>>hba_file
>> --
>> /etc/postgresql/8.4/main/pg_hba.conf
>> (1 row)
>>
>> Moreover, is there anyway to view content of this file from stored in
>> above
>> location "Techdb" command prompt itself.
>>
>> Techdb=# cat /etc/postgresql/8.4/main/pg_hba.conf;
>
> Probably pg_read_file():
>
> select pg_read_file('pg_hba.conf', 0, 8192);
>
> Note that pg_read_file only allows paths relative to $PGDATA, which is
> what you get from SHOW data_directory;
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Since the pg_hba.conf file is located in the /etc path, the pg_read_file
command fails with an error of
"could not open file "pg_hba.conf" for reading: No such file or direcotry"
It also won't allow use of the absolute path.
The answer I found was to use the following command:
postgres=# \! exec cat /etc/postgresql/8.3/main/pg_hba.conf
Tim
--
Timothy J. Bruce
Registered Linux User #325725
--
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] Who is locking me?
On Thu, March 18, 2010 12:29, Bill Moran wrote: > In response to "Abraham, Danny" : > >> Hi, >> >> >> >> A process hangs forever. >> >> When using this query... >> >> >> >> SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, >> >>pg_stat_get_backend_activity(s.backendid) AS current_query >> >> FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s where >> pg_stat_get_backend_activity(s.backendid) not like '> >> and pg_stat_get_backend_activity(s.backendid) not like ''; >> >> >> >> >> >> I get... >> >> >> >> >> >> procpid |current_query >> >> -+-- >> >> -+--- >> >>26702 | SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, >> >> :pg_stat_get_backend_activity(s.backendid) AS >> current_query >> >> : FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS >> s >> >> : where pg_stat_get_backend_activity(s.backendid) not like >> '> >> : and pg_stat_get_backend_activity(s.backendid) not like >> ''; >> >>21282 | update cmr_lastno set lastisn=lastisn+1 where >> tablename='DANNY'; >> >>25936 | in transaction <== This is the locker >> >> >> >> And the question: >> >> - What exactly is the locker doing? > > Idle in transaction means they aren't doing anything, but have not yet > committed or rolled back the transaction, thus any locks they took out > early in the transaction are still held. > > Keep in mind that they aren't doing anything at that instant. It's > possible > that the connection is still working, but only issuing queries every so > often, because it's processing the resultant data. (It's also possible > that they really aren't doing anything and should be swatted for leaving > the transaction open). > >> - Can I retrieve the Client PID somewhere (then by process name I will >> dive into the code). > > If you're on a POSIX system, you can use netstat to find out the details > of > the socket the backend is connected to. If it's a local connection, > another > look at netstat will give you the pid of the client. If it's a remote > connection, then you'll have the client IP and port #. Depending on your > setup, the client IP alone might tell you what you need to know. If > that's > not enough, you should be able to use the client port # on the client's > system to track down what process is on the client end (again, using > netstat) > > If you're not on a POSIX system, you can probably still do what I > described, > I just don't know the details of how it's done. Windows has a netstat > equivalent, I think. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- Yes, Windows does have Netstat (C:\Windows\System32 under Windows XP). Use the -b option to determine which program is using the port. Tim -- Timothy J. Bruce Registered Linux User #325725 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
