Re: [GENERAL] PostgreSQL Timezone and Brazilian DST
On 2015-10-27 20:29, Edson Richter wrote: > Hi! > > Using PostgreSQL 9.3.10 x86_64 Oracle EL7 compiled by gcc (GCC) 4.8.3 > 20140911, installed using yum repository. > > In postgresql.conf, I do have: > > timezone="America/Sao_Paulo" > > Since DST is in place in Brazil, it is enough to "systemctl reload > postgresql-9.3" to make it effective? > Or a complete restart is required? First a side note, if you work with systems and people in more than one time zone, I strongly recommend and it will make your life much simpler if you configure all your servers in UTC (looking at logs, reasoning about automated/cron jobs etc...). Note that you can set the timezone on a per session basis with `set timezone="America/Sao_Paulo"`. Now if you do want to work in local time zone, so America/Sao Paulo in your case: If the timezone is set correctly, we do not need to re-start anything when the DST switch happens. Postgresql will follow the time zone set at the OS level, run the command `timedatectl` to check if your RedHat is set to what you expect. In postgres, use `show timezone` and verify that it says "localtime". If you want postgres to use at a different time zone than the OS, then do configure timezone in postgresql.conf, and yes you will need to restart postgres once you have made that change. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] does pg_dump get delayed if I have a loop that does continuous insertions
On 2015-10-28 14:09, anj patnaik wrote: > > Also, I want to know if anyone has found any handy cron scripts for automated > backups to run on a daily/weekly basis? i found some on google, but interested > to know if there are better ones. It does a lot more but: https://github.com/wal-e/wal-e -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] strange connection problem.
On 2015-10-23 08:29, Day, David wrote: > I have a development site ( FreeBSD 10.1 and Postgres 9.3 ) where I can > connect to the database via psql or pgadminIII both locally and remotely. > > However, all of the local apps ( 3 different ones ) that would connect to it > are failing to connect. Sounds like your apps are using a different port than the default install. Use "psql -p " with the port from the URI configured in your apps. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] PSQL Tools
On 2015-10-18 16:37, Nicolas Paris wrote: > > I didn't know DBeaver, it looks great ! (I personnally have many bugs/freeze > with pgadmin3 on ubuntu, moreover the interface is not userfriendly) It also has the advantage of working with all the major database engines, so you can use the same tool across all of them. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] PSQL Tools
On 2015-10-18 08:04, jwienc...@comcast.net wrote: > Hello > > Is anyone aware of any tools like TOAD that are available for Postgresql? DBeaver works will all kinds of databases, DB2, Sybase, Oracle, SQLite, MySQL and PostgreSQL. It's written in Java and runs anywhere a JVM will: http://dbeaver.jkiss.org/ PostgreSQL only: http://www.pgadmin.org/ -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] Use tar to online backup has an error
On 2015-09-24 03:55, maxiangqian wrote: > when I use tar to online backup ,It has an error: > > /bin/tar: Removing leading `/' from member names This is not an error, more like a warning. GNU tar doesn't not store the full path in the tar file, but a relative path, as full paths are considered dangerous (easier to overwrite existing data by mistake). > /bin/tar: /data/test/PG_9.0_201008051/20001/2451: Cannot stat: No such > file or directory > > /bin/tar: Error exit delayed from previous errors > > > and /data/test/PG_9.0_201008051/20001/2451 file is not exist. > It looks like you are trying to make a filesystem backup while the files are still being updated. In order to make a filesystem backup you need to either: - shutdown the database - run "pg_start_backup" (make SURE that you run pg_stop_backup" when done) http://www.postgresql.org/docs/9.3/static/backup-file.html http://www.postgresql.org/docs/9.3/static/continuous-archiving.html#BACKUP-TIPS -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] hot backup with zfs ?
On 2015-08-20 07:50, Laurent Laborde wrote: I'm wondering if anyone tried the following hot backup process, and if it works : pg_start_backup() zfs snapshop pg_stop_backup() copy the snapshot to the backup server. I have done this with AWS EBS snapshots, created new volumes from the snapshots and created a new server with those volumes, and it works well. I can't see why it'd be different with ZFS. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] Sorting CSV string and removing Duplicates
I have a csv string in a text field that is unsorted and contains duplicates. Is there a simple way to remove these and sort the string. E.g 2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27 Do you need to eventually load the data in Postgres? I'd personally use python to deal with this, we're talking 4 or 5 lines here, if even. I suspect you can do the same with perl or ruby or whatever is your weapon of choice. How columns does your csv file has? Is it a one-line file? -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] I lost my password
On 2015-07-26 20:51, p...@arbolone.ca wrote: After installing PostgreSQL I lost the password, how can I recover it? Assuming you have local login configured (which is the default), you can su to the postgres user (or whichever is configured in pg_hba.conf) and login with psql directly without being challenged for a password. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] select count(*);
On 2015-06-11 08:20, Geoff Winkless wrote: On 11 June 2015 at 15:17, Marc Mamin m.ma...@intershop.de mailto:m.ma...@intershop.dewrote: But COUNT(*) does have meaning - it means the number of rows. which rows? :-) The number of rows in the query, as well you know :) But interestingly postgres=# select count(*) where 1=0; count --- 0 (1 row) -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] MD5 password storage - should be the same everywhere?
On 2015-05-25 17:58, Adrian Klaver wrote: On 05/25/2015 01:41 PM, Francisco Reyes wrote: On multiple machines, should the MD5 be the same? using select rolname, rolpassword,rolcanlogin from pg_catalog.pg_authid where rolname = 'SomeUser'; Should the MD5 be the same? I understood that is just a md5 hash of the password and the username with the string md5 pre-appended, so it should be the same. On version 9 definitely, as documented: http://www.postgresql.org/docs/9.3/static/catalog-pg-authid.html The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe. Although I'm surprised it's not seeded, or even using a strong hash, but that's a different subject. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] MD5 password storage - should be the same everywhere?
On 2015-05-25 17:58, Adrian Klaver wrote: On 05/25/2015 01:41 PM, Francisco Reyes wrote: On multiple machines, should the MD5 be the same? using select rolname, rolpassword,rolcanlogin from pg_catalog.pg_authid where rolname = 'SomeUser'; Should the MD5 be the same? I understood that is just a md5 hash of the password and the username with the string md5 pre-appended, so it should be the same. On version 9 definitely, as documented: http://www.postgresql.org/docs/9.3/static/catalog-pg-authid.html The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe. Although I'm surprised it's not seeded, or even using a strong hash, but that's a different subject. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] Index on integer or on string field
On 2015-05-15 18:31, Scott Marlowe wrote: On Fri, May 15, 2015 at 9:18 AM, Job j...@colliniconsulting.it wrote: i have a table of about 10 millions of records, with the index on a string field. Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload. The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names. Is index are integer and not characteral, performance are better and workload reduces? Is there any comparisons? Have you considered using a multi-column index here? if there's a more selective field you could index along with your rather non-selective one that might work better. But it's hard to tell without looking at you database usage etc. To add to Scott suggestion, think about what columns you include in your queries when you are looking for a unique row (what columns are on the right hand side of the WHEN clause). This will help you narrow down what makes unique conditions on your table, from which you can create a better index. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] PGConf NYC 2015 videos
On 2015-05-13 07:37, Jim Mlodgenski wrote: Yes, the talks were recorded. There was a lot of content so we are still working on getting everything together. We hope to have them up soon. Will you be posting on this list when they are ready? -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] SELECT INTO and ON COMMIT
On Wed, May 13, 2015 at 05:29:36PM -0600, Yves Dorfsman wrote: Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE? On 2015-05-13 17:56, David G. Johnston wrote: From the documentation of SELECT INTO The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code. http://www.postgresql.org/docs/9.4/interactive/sql-createtableas.html Which effectively means consider the feature deprecated. Especially since CREATE TABLE is standard conforming and SELECT INTO is not. Ah! This works. Thanks. Will `SELECT INTO` be deprecated? It is very convenient when writing pgplsql functions, to select into a record. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT INTO and ON COMMIT
Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE? Thanks. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] detached query?
On 9.3, is there any way to start a query, detach from the server and have the query keep going (long query that updates tables, but nothing is returned)? Thanks. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] plpgsql functions organisation
As for performance concerns, in 99% of cases code maintainability is going to be way more important than performance microoptimization. If you're *that* concerned about performance than plpgsql probably isn't the right answer anyway. Isn't one of the advantage of running on the server to avoid data round trip? What would you recommend for better performance? -- http://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] schedulers
I just want to confirm that currently there is no scheduler that isn't dependent on a crontab (like PgAgent), that could be run entirely from within PostgreSQL only. Anybody knows of one? -- http://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql functions organisation
I find my plpgsql functions becomes unreadable very quickly. I want to break them up in smaller functions. What is the best way to organised them? Is there any way to define functions inside functions? When I list functions in psql, I can see them all at the same level, is there any way to organise them in packages like with python and other languages, so the smaller functions are hidden away in a package/directory? Thanks. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] plpgsql functions organisation
On 2015-05-02 11:12, Melvin Davidson wrote: AFAIK, you cannot package functions in PostgreSQL, but it is possible to call a function from within a function. That being said, I would seriously look at how and why you are writing your functions as functions that call other functions are not very efficient. Simply to make long procedures easier to read and follow the logic. Also note that PostgreSQL allows you define functions using Python, so that might be a possible compromise. Thanks. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] checkpoint
Hi, If I run checkpoint from psql, is it applied to all the databases? What if I do it though an API? When connecting with psycopg2, I'm forced to specify a database name, if I use dbname=postgres, and execute checkpoint;, is it applied to all the databases? Thanks. -- Yves. -- 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] checkpoint
On 2014-07-10 13:02, Guillaume Lelarge wrote: 2014-07-10 20:56 GMT+02:00 Yves Dorfsman y...@zioup.com mailto:y...@zioup.com: Hi, If I run checkpoint from psql, is it applied to all the databases? What if I do it though an API? When connecting with psycopg2, I'm forced to specify a database name, if I use dbname=postgres, and execute checkpoint;, is it applied to all the databases? Yes. CHECKPOINT is more a cluster-wide operation, rather than a database one. This is what I had observed, but wanted to be 100% sure. Thanks. -- Yves. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Postgresql and Postmaster response
Sean [EMAIL PROTECTED] wrote: I am using PostgreSQL version 7.0.2 with RedHat 6.1. When I start up the postmaster at the prompt, I get the following response and the session hangs and doesn't return to the prompt but I am able to use my databases and PostgreSQL. Is this normal or correct? If not, what should I do to fix this problem? Your session hasn't hung. Basically you are running a server in the foreground. You really want to run it in the background, so add an '' (ampersand) at the end of your line and the server will be sent to the background. Ideally you want to nohup it too. From the man page: USAGE To start postmaster using default values, type: % nohup postmaster logfile 21 This command will start up postmaster on the default port (5432). This is the simplest and most common way to start the postmaster. By the way, since you are running on Linux, did you install postgreSQL from a package ? If yes, there probably is a postgres file in /etc/init.d, or /etc/rc something (not sure what it is on redhat, /etc/rc.d/init.d ??). Check it, than the best way to sopt/start your server would be: /etc//postgres stop /etc//postgres start Yves. Yves Dorfsman[EMAIL PROTECTED] http://www.cuug.ab.ca/~dorfsmay
[GENERAL] Re: How to remove a user ?
Fred [EMAIL PROTECTED] wrote: How to remove a user in postgres ? either: dropuser username (from the command line) or DROP USER username; (as an SQL statement) Yves. Yves Dorfsman[EMAIL PROTECTED] http://www.cuug.ab.ca/~dorfsmay
[GENERAL] Re: How to list and remove a user in postgres ?
HomerWelch [EMAIL PROTECTED] wrote: (\h createuser.) I don't recall a method for listing all users. man psql has a lot of good information. Log in as postgres (or whoever is your postgres super user), then run psql, and: \dS Will give you a list of the system tables (relations). \dS pg_user Will give you the structure of that table, and actually some more help. SELECT usename, usesuper from pg_shadow; Will give you a list of the users that are define, and indicate which one is the super user. Yves. Yves Dorfsman[EMAIL PROTECTED] http://www.cuug.ab.ca/~dorfsmay
[GENERAL] disabling transactions
Hi, Is there any way to disable transactions on some databases in postgreSQL ? The reason I'm asking is because I need a very fast read only DB. So I'm thinking of having the real DB fully functional, and once or twice a day dump it into a non-transactional read-only one, for the read only side of the world. If I can't do it with postgres, I'm thinking of using mySQL for the read only part, but I would much prefer to use postgres, since I'm using it on the read/write side. Thanks, Yves. Yves Dorfsman[EMAIL PROTECTED] http://www.cuug.ab.ca/~dorfsmay
[GENERAL] images
Is it possible to save images in postgreSQL ?? If yes, and in the case of a web site using both postgrSQL and mod_perl, is it preferable (faster) to save images in the database, or to simply save a reference to the images in the database, but load the image from the filesystem ??? Yves. Yves Dorfsman[EMAIL PROTECTED] http://www.cuug.ab.ca/~dorfsmay
[GENERAL] raw devices
Is there any way to use raw devices within postgreSQL, or does it support file system only ?? Is there any reason for not supporting raw device, or is it just a question of not having the time to write a driver ? Yves. Yves Dorfsman[EMAIL PROTECTED] http://www.cuug.ab.ca/~dorfsmay
