Re: [GENERAL] DATA Integrity & Recovery

On Wed, 12 Jul 2017, chris faber wrote: I would appreciate the communities help in the following: 1. Determine if data from the incremental backups can be restored or recovered. 2. Determine if data can be recovered from individual files backed up from main Postgres data directory. Chris,

Re: [GENERAL] INSERT INTO: string with apostrophe

On Thu, 29 Jun 2017, Cachique wrote: That is correct. You can double the single quotes. Another way is to use the E'...' syntax (i.e., E'O\'Brien'). Or you can use the quote_* functions ( https://www.postgresql.org/docs/current/static/functions-string.html) Walter, Thanks for confirming.

[GENERAL] INSERT INTO: string with apostrophe

The syntax for inserting data into a table uses single quotes to identify strings. When I have a string such as O'Brien do I double the single quotes within the string (i.e., 'O''Brien') or is there another way to include such strings? Rich -- Sent via pgsql-general mailing list

Re: [GENERAL] Postgres backup solution

On Tue, 14 Mar 2017, Adrian Klaver wrote: What would be a recommended solution for backing up a very large Postgres (~13TeraBytes) database in order to prevent from data deletion/corruption. Current setup is only to backup/restore to a standby read-only Postgres server via AWS S3 using wal-e

Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

On Fri, 10 Mar 2017, Adrian Klaver wrote: That this looks like a merge of the sample file: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/libpq/pg_hba.conf.sample;h=6b1778a72136edf52cea56f2ab088b9449df9a48;hb=HEAD with your additions. Seems you have not restarted

Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start [FIXED]

On Fri, 10 Mar 2017, Tom Lane wrote: The "@remove-line-for-nolocal@" bits should not be there. initdb would normally either delete those lines entirely, or strip off "@remove-line-for-nolocal@", depending on the switches it was given. Likewise the various other @something@ bits should have been

Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

On Fri, 10 Mar 2017, Rich Shepard wrote: Actually, it has the modifications I've made over the years. That's why I could not see what changed. Well, postgres was running yesterday and allowed me to access my bookkeeping software so obviously something did change without my manually editing

Re: [GENERAL] INSERT and ON CONFLICT

On Fri, 10 Mar 2017, Brian Dunavant wrote: I believe the following test should answer your question. Thank you, Brian. It does answer my question. Regards, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

On Fri, 10 Mar 2017, Tom Lane wrote: It looks like what you have in pg_hba.conf is a raw copy of pg_hba.conf.sample, without any of the editing that initdb normally applies to it (to say nothing of manual adjustments you might make later). Tom, Actually, it has the modifications I've made

[GENERAL] INSERT and ON CONFLICT

I'm filling a table with rows and have the first batch successfully inserted. When I add more rows there may be some that already exist in the table and I would prefer that they be ignored and the insert process continue. The syntax page for INSERT suggests that ON CONFLICT DO NOTHING is

[GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

I've tried resolving this but do not see what changed. 1. Shut down postgres: pg_ctl stop -D /var/lib/pgsql/9.6/data 2. Upgraded version (using SlackBuilds.org package as usual). 3. Tried re-starting postgres: postgres@salmo:~$ postgres -D /var/lib/pgsql/9.6/data/ & [1] 17585

Re: [GENERAL] New 9.6.2 installation lacks /usr/lib/postgresql/ [RESOLVED]

On Sat, 25 Feb 2017, Rich Shepard wrote: Just installed postgresql-9.6.2 on Slackware-14.2/x86_64 and initialized the database. However, there's no /usr/lib/postgresql/ directory with its subdirectories. Duh! It's in /usr/lib64/postgresql. Sigh, Rich -- Sent via pgsql-general mailing

Re: [GENERAL] New 9.6.2 installation lacks /usr/lib/postgresql/

On Sat, 25 Feb 2017, Adrian Klaver wrote: What application? GRASS . There is, it is just not at /usr/lib/postgresql. Search for plpgsql.so Ah! That's what I need to know. As to the build you will either need to specify where the lib/ is in the configuration

[GENERAL] New 9.6.2 installation lacks /usr/lib/postgresql/

Just installed postgresql-9.6.2 on Slackware-14.2/x86_64 and initialized the database. However, there's no /usr/lib/postgresql/ directory with its subdirectories, and another application I'm building wants that location during configuration. Postgres was installed via the SlackBuilds.org

Re: [GENERAL] COPY to question [ANSWERED]

On Tue, 17 Jan 2017, Tom Lane wrote: Use psql's \copy instead. Thanks, Tom. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] COPY to question

Running -9.6.1. I have a database created and owned by me, but cannot copy a table to my home directory. Postgres tells me it cannot write to that directory. The only way to copy tables to files is by doing so as the superuser (postgres). Why is this, and can I change something so I, as a

Re: [GENERAL] 9.6.1: INSERT with PK as serial

On Mon, 16 Jan 2017, David G. Johnston wrote: Without any special effort on your part the first 40 records you try to insert using the default sequence are now going to fail with duplicate key errors. David, Thank you. I added them while thrashing around looking for the source of the

Re: [GENERAL] 9.6.1: INSERT with PK as serial [FIXED]

On Mon, 16 Jan 2017, John R Pierce wrote: the above isn't valid postgresql, as field names need to be in "doublequotes" not 'singlequotes'. John, I thought I had changed them from single to double, yet missed seeing that I did not do so. Mea culpa! I know better than that. That fixed

Re: [GENERAL] 9.6.1: INSERT with PK as serial

On Mon, 16 Jan 2017, Tom Lane wrote: It looks like something deleted the quote marks. How are you entering this SQL command, exactly? Tom, I noticed that, too. Here's the first line: INSERT INTO companies

Re: [GENERAL] 9.6.1: INSERT with PK as serial

On Tue, 17 Jan 2017, David Rowley wrote: You may have more luck getting an answer if you include the statement causing the problem. David, Here's an example: INSERT INTO companies VALUES (1,'AG Spray Inc.',,'PO Box

[GENERAL] 9.6.1: INSERT with PK as serial

I have a table with the primary key type of serial. The initial data load has 40 lines, and each has a value for every column including sequential integers from 1 through 40 for the data. When I try to read it in using 'psql -d -f ' I get a syntax error at the second word in the string that is

Re: [GENERAL] Default column value [ANSWERED]

On Fri, 30 Dec 2016, David G. Johnston wrote: "The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed." NULL == "UNKNOWN" David, I forgot

Re: [GENERAL] Default column value

On Fri, 30 Dec 2016, Adrian Klaver wrote: DEFAULT is what is the column is set to if the user does not specify a value. As shown above a user can supply a NULL value. To guard against that the NOT NULL constraint is required. One more case I'd appreciate being clarified: when the column's

Re: [GENERAL] Default column value

On Fri, 30 Dec 2016, Adrian Klaver wrote: DEFAULT is what is the column is set to if the user does not specify a value. As shown above a user can supply a NULL value. To guard against that the NOT NULL constraint is required. Thanks, Adrian. This was not clear to me when I read the manual.

Re: [GENERAL] Default column value

On Fri, 30 Dec 2016, Tom Lane wrote: No, because you can explicitly insert a null. DEFAULT only controls what happens when you omit the column in an INSERT command. tom, Thanks for clarifying. I did not pick this up from reading the manual and knew that NULL could be an explicitly-defined

[GENERAL] Default column value

Reading the 9.6 docs suggests an answer to my question, but does not explicitly answer it, so I ask here. If a column has a default value specified does this mean the column cannot contain a NULL value? In other words, is DEFAULT NOT NULL redundant? TIA, Rich -- Sent via pgsql-general

Re: [GENERAL] Book or other resource on Postgres-local code?

On Thu, 29 Dec 2016, Guyren Howe wrote: I would like to find a book or other resource about SQL server-side programming (stored procedures etc) best practices in general and for Postgres in particular. Start here: Rich --

Re: [GENERAL] Er Data Modeller for PostgreSQL

On Thu, 29 Dec 2016, Nicolas Paris wrote: Hi I'd like to tell about Sql Power Architect Nicholas, SPA was going to be my next re-examination after dbeaver. Since the latter easily accomplished what I needed I stopped there. Thanks for the reminder, Rich -- Sent via pgsql-general

Re: [GENERAL] Er Data Modeller for PostgreSQL

On Wed, 28 Dec 2016, Adrian Klaver wrote: An example from my machine that works: aklaver@tito:~/bin> java -jar schemaSpy_5.0.0.jar -t pgsql -s public -u postgres -db production -host localhost -dp /home/aklaver/bin/postgresql-9.4.1212.jre6.jar -o s_spy Adrian, That's interesting. I

Re: [GENERAL] Er Data Modeller for PostgreSQL

On Fri, 23 Dec 2016, Thomas Kellerer wrote: Scott Mead just blogged about using SchemaSpy with Postgres Thomas, I've spent the past two days without success trying to get schemaSpy running here and have found a solution that works quickly and easily: dbeaver .

Re: [GENERAL] Securing Information

On Tue, 27 Dec 2016, Chris Weekes wrote: I was wondering what steps if any need to be taken to ensure that the patient and operational data is secure on a machine and or across the network. Chris, I'm far from an expert but until more knowledgeable folks respond I'll offer a couple of

Re: [GENERAL] Generating sample data

On Wed, 28 Dec 2016, Martijn Tonies (Upscene Productions) wrote: Not open source, but also not pricey (IMO): Advanced Data Generator. http://www.upscene.com/advanced_data_generator/ Generates e-mail addresses, street names, first & last names, company names, complex relationships etc. And

Re: [GENERAL] Generating sample data

On Tue, 27 Dec 2016, Adrian Klaver wrote: As it happens there is a Python version of the a fore mentioned faker: https://pypi.python.org/pypi/Faker/0.7.7 Adrian, Impressive and complete. It will generate all the data I need. Many thanks, Rich -- Sent via pgsql-general mailing list

Re: [GENERAL] Generating sample data

On Tue, 27 Dec 2016, Adrian Klaver wrote: As it happens there is a Python version of the a fore mentioned faker: https://pypi.python.org/pypi/Faker/0.7.7 It was I use to generate fake/sample data. Adrian, Aha! That's a great start for me. Many thanks, Rich -- Sent via pgsql-general

Re: [GENERAL] Generating sample data

On Tue, 27 Dec 2016, Adrian Klaver wrote: What sort of data do you want to create? Adrian, Various text, date, and numeric values. If it is data specific to a field of study then things might get trickier. It's not a common database. I'll probably need to cobble together generic data

Re: [GENERAL] Generating sample data

On Tue, 27 Dec 2016, Steve Crawford wrote: You could start here: http://www.softwaretestingmagazine.com/tools/open-source-test-data-generators/ I have rolled my own on occasion by just pulling some public lists of most common given names and family names and toing a full-join. Same for city,

Re: [GENERAL] Generating sample data

On Tue, 27 Dec 2016, Greg Navis wrote: In the Ruby land there's a gem called faker that allows you to generate fake data. However, I'm not sure it can generate data based on a schema so a little bit of scripting my be necessary. Would this approach work for

[GENERAL] Generating sample data

My previous databases used real client (or my own) data; now I want to generate sample data for the tables in the two applications I'm developing. My web search finds a bunch of pricey (IMO) commercial products. Are there any open source data generators that can provide sample data based on

Re: [GENERAL] Error in column constraint syntax

On Tue, 27 Dec 2016, David G. Johnston wrote: ​5.3.1 is instructional. The SQL Command chapter is the authoritative source for syntax. David, I'll read that, too. CHECK ( expression ) [ NO INHERIT ] | -- you are missing the mandatory ( ) surrounding your expression All of the examples

[GENERAL] Error in column constraint syntax

I'm encountering DDL syntax errors using postgres-9.6.1 that I've not before seen, and I'm having a difficult time finding the cause when reading the appropriate sections of the manual; Section 5.3.1 in this case: CREATE TABLE Agencies ( org_name VARCHAR(48) PRIMARY KEY, acronym VARCHAR(8)

Re: [GENERAL] Syntax error needs fresh eyeballs

On Mon, 26 Dec 2016, Tom Lane wrote: ERROR: there is no unique constraint matching given keys for referenced table "weather_params". because Weather_Params.param isn't constrained to be unique. ... Seeing that Weather_Data also has a site_id column, I'm going to guess that what you wanted

[GENERAL] Syntax error needs fresh eyeballs

My schema includes three tables and psql throws an error I'm not seeing when I try to read the schema into the database. I'd appreciate fresh eyes looking at the table and learning what error I'm not seeing. The tables: CREATE TABLE Weather ( site_id INTEGER PRIMARY KEY, site_name TEXT,

Re: [GENERAL] CRM where pg is a first class citizen?

On Tue, 13 Dec 2016, Adrian Klaver wrote: This killed the community(Open Source) edition going forward: https://community.sugarcrm.com/thread/18434 I'd like to comment regarding this paragraph from the above-referenced blog post: "In the course of the past five years, we have surveyed tens

Re: [GENERAL] CRM where pg is a first class citizen?

On Tue, 13 Dec 2016, John R Pierce wrote: CRM (Customer Relationship Manager) != CMS (Content Management System). John, True, and Django can be used for a CRM; there's an example by a North Carolina shop. Search the web for "django crm". I was going to use django but decided to make my CRM

Re: [GENERAL] Looking for an online mentor

On Thu, 8 Dec 2016, Metare Solve wrote: Will Python enable me to do the same things that I do with that kind of big data processing program? Yes. I need SOMETHING to use for analysis for the tests I'm going to have to take when I job hunt so I'm exploring KNIME right now and doing the

Re: [GENERAL] Looking for an online mentor

On Thu, 8 Dec 2016, Adrian Klaver wrote: GUI's only get you so far. At some point you will need to dive deeper to get what you. I am mostly a self taught programmer(biologist by training) so I understand the hill you are facing. The language I use is Python, mainly because to me it made sense.

Re: [GENERAL] PDF files: to store in database or not

On Thu, 8 Dec 2016, Chris Travers wrote: Assuming relatively small files, bytea makes much more sense than a large object. Hi Chris, Most of the documents are only a few pages in size. LOBs work best when you need a streaming interface (seek and friends) while bytea's are otherwise much

Re: [GENERAL] PDF files: to store in database or not

On Thu, 8 Dec 2016, Adrian Klaver wrote: http://initd.org/psycopg/docs/usage.html?highlight=binary#adapt-binary Thanks again, Adrian. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] PDF files: to store in database or not

On Thu, 8 Dec 2016, John DeSoi wrote: I have been storing PDFs in Postgres for several years without any problems. Documents range in size from a few pages to 100+ pages. I'm using a bytea column, not large objects. I store the documents in a separate database from the rest of the application

Re: [GENERAL] When to use COMMENT vs --

On Thu, 8 Dec 2016, Charles Clavadetscher wrote: IMHO the fact mentioned by Chris Travers that comments on objects included in the database can be used by tools to generate the documentation is probably the most important (besides their being persisted). Charles, Chris' contribution was

[GENERAL] When to use COMMENT vs --

I have used '-- ' to enter comments about tables or columns and am curious about the value of storing comments in tables using the COMMENT key word. When is the latter more appropriate than the former? TIA, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] PDF files: to store in database or not

On Tue, 6 Dec 2016, John R Pierce wrote: indeed BYTEA is postgres's type for storing arbitrary binary objects that are called BLOB in certain other databases. John, I thought so. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] PDF files: to store in database or not

On Tue, 6 Dec 2016, David Wall wrote: The advantages of storing in the database is that a DB backup will have everything, instead of a DB backup and a file system backup. Using a BLOB, you can certainly keep track of variable length PDFs. David, I did not realize that a BLOB is not the

Re: [GENERAL] PDF files: to store in database or not

On Tue, 6 Dec 2016, Joshua D. Drake wrote: Due to the widely variable size of a PDF document, I would say no. I would store the metadata and file location. Joshua, I read your answer as "don't store them in the database, but store the location in a column." Thanks for confirming, Rich

[GENERAL] PDF files: to store in database or not

With no experience of storing binary data in a bytea column I don't know when its use is appropriate. I suspect that for an application I'm developing it would be better to store row-related documents outside the database, and want to learn if that is the appropriate approach. Consider an

Re: [GENERAL] Postgres and LibreOffice's 'Base'

On Sun, 4 Dec 2016, Martin Collins wrote: The problem with kexi is that it does not run on Microsoft OSes which is what my clients use. There is a Windows package in the works, apparently. Version 3 already builds on Windows. Martin, However, it is highly unlikely that my clients (all

Re: [GENERAL] Postgres and LibreOffice's 'Base'

On Mon, 5 Dec 2016, Johann Spies wrote: Web2py is another (and maybe simpler) alternative to Django. Johann, Thank you. I'll look at it. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Postgres and LibreOffice's 'Base'

On Sun, 4 Dec 2016, John McKown wrote: I'm not really sure what ".. make it an application that my clients can use. ..." really means. John, It has to be simple and useful to naive users. I guess it means that you have some code for an application (which uses PostgreSQL as it's data

Re: [GENERAL] Postgres and LibreOffice's 'Base'

On Sun, 4 Dec 2016, Martin Collins wrote: Not tried it myself but have you seen https://www.kde.org/applications/office/kexi/ And if that's no good http://flask.pocoo.org/ is simpler than Django. Martin, The problem with kexi is that it does not run on Microsoft OSes which is what my

Re: [GENERAL] Postgres and LibreOffice's 'Base'

On Sun, 4 Dec 2016, Adrian Klaver wrote: The important questions that forgot to ask before, is how do you plan to deploy this: Or are you looking to deploy a front end/database combination to each client? This way. I've no interest or expertise in maintaining a web site (my ISP does this

Re: [GENERAL] Postgres and LibreOffice's 'Base'

On Sun, 4 Dec 2016, Adrian Klaver wrote: That is why I ended up learning Django, I could not find a GUI that was cross platform and had the capabilities I needed. Others might have suggestions. It would help though if you could list what you want in a GUI platform: OK. I spent a lot of time

Re: [GENERAL] Postgres and LibreOffice's 'Base'

On Sun, 4 Dec 2016, Adrian Klaver wrote: Base is basically a GUI over the database. Adrian, That's what I assumed it to be. That being said my experience with Base is that is not up to the task. It worked for me early on, but development on Base lagged relative to the other modules in

[GENERAL] Postgres and LibreOffice's 'Base'

I have postgres tables (with data) for a specific application but have not found the time to learn django to make it an application that my clients can use. It occurs to me that the most parsimonious approach is to use LibreOffice's Base for the UI with postgres as the dbms-engine. While I

Re: [GENERAL] Invoice Table Design

On Tue, 29 Nov 2016, bto...@computer.org wrote: The other bit of experience I'll share is the suggestion that invoicing is a situation that lends itself to the uniformly incremented sequence pattern. Accountants and comptrollers love this. Reading your message brought to mind a suggestion

Re: [GENERAL] Invoice Table Design

On Thu, 24 Nov 2016, Robert Heinen wrote: A quick intro -- I'm helping a company switch from a mongo database over to postgresql (yay!). The company is a marketplace app for musicians and hosts. The basic idea is that a host can book a musician for an event, like a wedding or a birthday. Also,

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

On Tue, 15 Nov 2016, Adrian Klaver wrote: To add to my previous post. If you do decide to follow the pg_upgrade procedure in the README do a pg_dump of the 9.5 data just before you do pg_ugrade and store it away in a safe place. The first time through a new process does not always end well:)

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

On Wed, 16 Nov 2016, Alban Hertroys wrote: pg_upgrade migrates your databases from your old (9.5) cluster to the new (9.6) one. Initdb doesn't do that. Alban, That's what I assumed to be the case. If your 9.6 database does indeed contain your databases, then something must have done the

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

On Tue, 15 Nov 2016, Rich Shepard wrote: $ /usr/bin/pg_ctl --version pg_ctl (PostgreSQL) 9.6.1 ls -al /usr/bin/pg_ctl lrwxrwxrwx 1 root root 32 Nov 15 14:16 /usr/bin/pg_ctl -> ../lib/postgresql/9.6/bin/pg_ctl* To increase my understanding I want to resolve an apparent discrepa

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

On Tue, 15 Nov 2016, Adrian Klaver wrote: So what do you get with /usr/bin/pg_ctl --version Interesting: $ /usr/bin/pg_ctl --version pg_ctl (PostgreSQL) 9.6.1 and given John's suggestion: ls -al /usr/bin/pg_ctl ls -al /usr/bin/pg_ctl lrwxrwxrwx 1 root root 32 Nov 15 14:16

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

On Tue, 15 Nov 2016, Adrian Klaver wrote: Rich is using Slackware and I am pretty sure it marches to a different drummer. Adrian, And a different band. Pat likes MariaDB which replaced MySQL so PostgreSQL is not part of the base distribution. But, SlackBuilds.org provides a build script

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

On Tue, 15 Nov 2016, Adrian Klaver wrote: Assuming the bindirs are in your $PATH: aklaver@panda:~> whereis -f pg_ctl Adrian, In my case: $ whereis -f pg_ctl pg_ctl: /bin/pg_ctl /usr/bin/pg_ctl /usr/X11R6/bin/pg_ctl /usr/bin/X11/pg_ctl /usr/X11/bin/pg_ctl /usr/man/man1/pg_ctl.1

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

On Tue, 15 Nov 2016, John R Pierce wrote: 9.5 is considered a major version, 9.5.4 is a minor.this will change when 10 is released, from 10 on, major versions will be 10, 11, 12, ... John, So the upgrade from 9.5.4 to 9.6.1 is as I assumed: major. based on the data paths you gave,

[GENERAL] Upgrade from 9.5.4 to 9.6.1

All my previous version upgrades were performed by running pg_dumpall in the older version followed by running 'pgsql -f ...' to install the dumped .sql file, usually because the upgrade jumped several versions. Now I'd like to try the available postgres commands. The older version is

Re: [GENERAL] E-R diagram code U1

On Sat, 29 Oct 2016, Adrian Klaver wrote: http://stackoverflow.com/questions/5628533/what-does-i1-i2-u1-means-in-visio-database-relationship-diagram http://stackoverflow.com/questions/23533702/about-entity-relationship-diagram Thanks, Adrian. I suspected that I was not effectively

Re: [GENERAL] E-R diagram code U1

On Sat, 29 Oct 2016, Tom Lane wrote: What does U1 represent? Unique constraint, perhaps? I'm just guessing. Tom, That's my guess, too. I've not seen it used before now and did not know if it's a standard code for something or one specific to this agency. Thanks, Rich -- Sent via

[GENERAL] E-R diagram code U1

Looking at the entity-relationship diagram for a regulatory agency database they have attribute identifiers of PK, FKn, and U1. The first is the Primary Key and the second is a Foreign Key, but I've not before seen a 'Un' identifyier. I've no idea what DBMS they're using (but suspect Oracle).

Re: [GENERAL] Save query results to new table [RESOLVED]

On Thu, 27 Oct 2016, John R Pierce wrote: CREATE TABLE newtable AS SELECT ... https://www.postgresql.org/docs/9.5/static/sql-createtableas.html Thank you, John. Much appreciated, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] Save query results to new table

It's been several years since I worked with postgres so I tried searching the web for the correct syntax to save query results to a new table, but without results. I'm running postgresql-9.5.4 here. The query extracts 28162 rows from a table of 122365 rows. I thought 'save table as' would do

Re: [GENERAL] System crashed: fatal error restarting postgres

On Fri, 30 Sep 2016, Tom Lane wrote: Wrong permissions on /dev/shm, perhaps? Tom, Yes. I keep forgetting about this since I don't reboot this server/workstation often. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] System crashed: fatal error restarting postgres [FIXED]

On Fri, 30 Sep 2016, Adrian Klaver wrote: See here: https://www.postgresql.org/message-id/24208.1473724630%40sss.pgh.pa.us Ah, shoot! I completely forgot about this. Yes, I reset the perms on /dev/shm and that fixed the problem. My apologies to all, Rich -- Sent via pgsql-general

[GENERAL] System crashed: fatal error restarting postgres

A java application threw a major error when closing and it crashed my system (including wiping out most of /tmp). After rebooting I su'd to user postgres and tried to start postgresql-9.5.4, but this error displays: postgres@salmo:~$ FATAL: could not open shared memory segment

Re: [GENERAL] Server crashed, now cannot start postgres [FIXED]

On Mon, 12 Sep 2016, Tom Lane wrote: Hmm, AFAIK that's what it should be out of the box. Certainly on my RHEL6 machine I see Until I make the time to upgrade this host from Slackware-14.1 to -14.2 the kernel is 3.10.17-smp. Rich -- Sent via pgsql-general mailing list

Re: [GENERAL] Server crashed, now cannot start postgres [FIXED]

On Mon, 12 Sep 2016, Tom Lane wrote: A look at the code suggests this is shm_open() returning EACCES. Not sure why that's happening. If this is a Linux box, maybe the permissions on /dev/shm are bollixed? Tom, Yes, it's a linux box. And /dev/shm/ does have incorrect permissions (755).

[GENERAL] Server crashed, now cannot start postgres

Tried to compile 3 large programs at one time and the CPU overheated, shutting down the server. Now when I try to start postgres-9.5.4 (as the superuser, postgres) I get this result: postgres@salmo:~$ postgres -D /var/lib/pgsql/9.5/data/ & [1] 14544 postgres@salmo:~$ FATAL: could not open

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On Wed, 7 Sep 2016, Patrick B wrote: 2 - I've never done a Postgres upgrade before, can you give some guide here? Is usually a easy thing? How long can it take? Is a downtime needed? Patrick, I suggest the place to start is 'man pg_upgrade.' It is helpful. Rich -- Sent via pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Adrian Klaver wrote: Another thing that came to mind is compatibility with existing applications/clients. You say you have been running using trust and I am betting your client connection parameters reflect that. Now for connections methods that can 'see' the .pgpass file

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Adrian Klaver wrote: Well first, if you are going to use trust as your auth method then specifying a password is moot exercise. I tried adding an explicit password to ~/.pgpass with md5 as the auth method, but that didn't work so I went back to trust. That's served well

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Adrian Klaver wrote: You don't it directly. That information is supplied by Postgres when you do CREATE or ALTER ROLE. The -W switch just does that for the superuser(postgres in your case) when you initdb a new cluster. Adrian, OK. That makes sense. Sorry, old habits.

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

On Sun, 4 Sep 2016, Adrian Klaver wrote: I would take Charles's suggestion and set up a .pgpass file just to be safe. The file ~/.pgpass already exists, but without an explicit password. I added my password (plain text). The file already had perms 0600. Perhaps my password was rejected with

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Adrian Klaver wrote: -W is not referring to the OS user but the database superuser. Now in your case they have the same name, postgres. The settings in /etc/passwd are not relevant to what -W is doing. -W is referring to user information being stored in the cluster in the

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

On Sun, 4 Sep 2016, Adrian Klaver wrote: Just be aware that you now have a password for the postgres user and that if you ever do enable md5 you will need it. I would take Charles's suggestion and set up a .pgpass file just to be safe. Adrian, OK. I'll also read the page at the URL you

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Adrian Klaver wrote: -W is not referring to the OS user but the database superuser. Now in your case they have the same name, postgres. The settings in /etc/passwd are not relevant to what -W is doing. -W is referring to user information being stored in the cluster in the

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Charles Clavadetscher wrote: Well, there you have it. As Adrian suggested you may set temporarily the authentication method to trust, set yourself a password and change it back to md5. Charles, I've had a password on this LAN for almost 2 decades. I've not before set a

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

On Sun, 4 Sep 2016, Adrian Klaver wrote: Actually there is an important difference. In your 9.3 file you have set METHOD set to trust and in the 9.5 file it is set to md5, which is password. Set the METHOD to trust in your 9.5 file and restart the database. Now for the non-socket access methods

Re: [GENERAL] Upgrading using pg_dumpall

On Sat, 3 Sep 2016, Adrian Klaver wrote: Or if you are fine running the 9.5 instance at port 5432, what happens if you do?: psql -d postgres -U some_user -p 5432 $ psql -d postgres -U rshepard -p 5432 Password for user rshepard: FATAL: password authentication failed for user "rshepard"

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Adrian Klaver wrote: But the message you sent me offlist showed the 9.5 instance running. But now it's not running. How are you starting the instance? As superuser poostgres: pg_ctl start -D /var/lib/pgsql/data & After removing an orphaned postmaster.pid the

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Adrian Klaver wrote: Actually you already have. From an email that I just realized was offlist: "As superuser postgres connected to 9.5.4 'psql -l' shows all databases loaded: xxx | rshepard | UTF8 | C | en_US.UTF-8 | xxx| rshepard | UTF8

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Charles Clavadetscher wrote: Are you able to connect to the new instance with any user at all, e.g. with psql? If so you can use the command \du to list all users. Charles, No. The postmaster is not running; trying to start it requires a password which is also rejected.

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Adrian Klaver wrote: The above is not possible. If the postmaster was not running there would be no rejection error. Adrian, Yes. I conflated when the postmaster was running -- using the 9.3.4 server -- and now when it's not running. Now I'm wondering if I mistakenly

Re: [GENERAL] Upgrading using pg_dumpall

On Sun, 4 Sep 2016, Charles Clavadetscher wrote: Does the user rshepard exist in the new 9.5 instance? I assume so but do not know how to check this. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

  1   2   3   4   5   6   >