[GENERAL] Inherit Superuser Role Help

plication | {} web | Cannot login | {} Shouldn't 'carlos' be a superuser based on him being a member of a role which has createdb and superuser rights granted to it? -- Carlos Mennens -- Sent via pgsql-general mai

Re: [GENERAL] Role Authentication Failure

| {} postgres=# \password carlos Enter new password: Enter it again: Now I login as 'carlos': carlos@debian:~$ psql -d postgres Password: psql (9.1.9) Type "help" for help. postgres=> but... -- Carlos Mennens -- Sent via pgsql-general maili

Re: [GENERAL] Role Authentication Failure

o data yet. I just don't understand why granting a role to a user destroys his authentication and even REVOKE his dba group role doesn't fix 'carlos'. On Mon, Apr 15, 2013 at 11:27 AM, Adrian Klaver wrote: > > > On Mon, Apr 15, 2013 at 7:30 AM, Carlos Mennens &

[GENERAL] Role Authentication Failure

've removed 'dba' role from 'carlos' but he still can't login. My only option is to DROP the 'carlos' role and re-create him. The problem occurs when I: GRANT dba TO carlos; I don't understand which role (carlos or dba) needs INHERIT or NOINHERIT if

Re: [GENERAL] PostgreSQL Backup Booklet

___** > > See > http://www.peak6.com/email_**disclaimer/<http://www.peak6.com/email_disclaimer/>for > terms and conditions related to this email > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> > -- Carlos Mennens

[GENERAL] Can't Drop Role

ction pg_file_rename(text,text) owner of function pg_file_rename(text,text,text) owner of function pg_file_write(text,text,boolean) owner of extension adminpack -- Carlos Mennens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http:

Re: [GENERAL] Sample databases

onal values. Sadly unless you create one yourself, I don't think there's one publicly available that meets our expectations. -- Carlos Mennens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Dedicated PostgreSQL System

be dedicated to running Linux and PostgreSQL software. Can someone please help me understand a few things I need to view or test with in order to get the most utilization from PostgreSQL & the dedicated hardware it will sit on top? -- Carlos Mennens -- Sent via pgsql-general mailing list (pgs

Re: [GENERAL] Automated Backup Script Help (Linux)

On Wed, Mar 7, 2012 at 2:08 PM, John R Pierce wrote: > whats that pg_backup.sh script look like?  you're getting shell errors on > line 7 and 8 of the script. I didn't attach it since it's the same as the link I referenced in my original post. I'll attach it in here for those that don't or can't

[GENERAL] Automated Backup Script Help (Linux)

I'm trying to get the automated backup scripts to work from: http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux Currently I'm using PostgreSQL 9.1.3 and have the following three files: carlos@db1:~/postgresql$ ls -l total 20 drwxr-xr-x 2 carlos users 4096 Mar 7 13:13 backup -rw-r--r-- 1

[GENERAL] Defining Role Privileges

I'm wondering about my CREATE ROLE statements for PostgreSQL. I guess I don't know if there's an official answer but I feel like I'm entering a lot of redundant privileges to a role for example: CREATE ROLE tom NOINHERIT LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION; CREATE ROLE My question is

Re: [GENERAL] Puzzling full database lock

On Wed, Feb 1, 2012 at 7:51 PM, Christopher Opena wrote: > It's CentOS 5.5, PostgreSQL version 9.0.4 (x86_64). That seems extremely bleeding edge for CentOS. Did you compile this package from source RPM or some 3rd party package maintainer for PostgreSQL? -- Sent via pgsql-general mailing list

Re: [GENERAL] Puzzling full database lock

On Wed, Feb 1, 2012 at 7:38 PM, Christopher Opena wrote: > Hello folks, > > We've been running into some very strange issues of late with our PostgreSQL > database(s).  We have an issue where a couple of queries push high CPU on a > few of our processors and the entire database locks (reads, write

Re: [GENERAL] Data Type for Money

On Fri, Dec 30, 2011 at 12:46 PM, Adrian Klaver wrote: > My guess is it is listed as numeric which is equivalent to decimal: > > http://www.postgresql.org/docs/9.1/interactive/datatype-numeric.html Thanks. I just for some reason can't see or understand the difference between 'decimal' & 'numeric'

Re: [GENERAL] Dated Version of PostgreSQL

2011/12/30 Devrim GÜNDÜZ : > Hi, > > On Fri, 2011-12-30 at 11:50 -0500, Carlos Mennens wrote: >> My question is how exactly would I >> install the latest version of PostgreSQL (9.1.2) on RHEL 6.2? > > We have PostgreSQL yum repository: > > http://yum.postgresql.org

[GENERAL] Data Type for Money

I'm trying to understand what is the recommended data type for $ in PostgreSQL. I've done some research and from what I've gathered, there are a few options: 1. decimal 2. money I've read the 'money' data type is non-standard and I should avoid using this. I see it a bunch of Microsoft SQL Server

Re: [GENERAL] Dated Version of PostgreSQL

h your distro might help. > However, the worst that can happen is that it stops before compiling, so no > big deal. > > Bèrto > > > On 30 December 2011 18:59, Carlos Mennens wrote: >> >> I've never compiled anything from source. Is this difficult if I've

[GENERAL] Dated Version of PostgreSQL

I've been asked to stand up a dedicated database server for a new office. They're only requirement is the server run RHEL 6.2 64-bit. I told them no problem as I'm very familiar with Linux and installing / configuring PostgreSQL. So after I've installed RHEL 6.2, I then installed PostgreSQL and the

[GENERAL] How To Handle Hung Connections

I'm attempting to delete a database that I've obviously not closed connections from cleanly. postgres=# DROP DATABASE filters; ERROR: database "filters" is being accessed by other users DETAIL: There are 4 other session(s) using the database. How exactly would one manage this issue from a Postg

[GENERAL] Streaming Replication Configuration

I'm attempting today to get streaming replication from the Wiki configured on my two 9.1.2 servers: http://wiki.postgresql.org/wiki/Streaming_Replication I'm on step #6 6. Make a base backup by copying the primary server's data directory to the standby server. $ psql -c "SELECT pg_start_backup('

Re: [GENERAL] Server/Data Migration Advice

On Thu, Dec 15, 2011 at 11:09 AM, Raymond O'Donnell wrote: > The point here is that with the plain-text dump (the default output from > pg_dump), you can feed that directly to psql; but you have no control > over what is restored, or in what order, without editing the dump file > directly. > > If,

Re: [GENERAL] Server/Data Migration Advice

On Thu, Dec 15, 2011 at 10:28 AM, Adrian Klaver wrote: > The reason I pointed to the manual links is that there is a lot of good > information in there. It deserves more than a skim:). Realistically, to get > the > most out of the dump/restore process you need to know the options available on > b

Re: [GENERAL] Server/Data Migration Advice

On Thu, Dec 15, 2011 at 9:37 AM, Adrian Klaver wrote: > You know the fine manual covers this?: > http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html I've honestly skimmed the manual and it's very easy to use and good but it covers so many aspects and sometimes I need a specific answer

Re: [GENERAL] Server/Data Migration Advice

On Mon, Dec 12, 2011 at 12:23 PM, Andreas Kretschmer wrote: > - use the pg_dumpall from the new version to make the dump, for instance >  pg_dumpall -h ... | psql (something like this, on the new >  host) I performed a pg_dump from my new 9.1.2 server but my question now is importing the data. S

Re: [GENERAL] Locking Tables & Backup Inquiry

On Wed, Dec 14, 2011 at 1:38 PM, Andy Colson wrote: > this'll run every hour. > >> 0 * * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql Thank you! > try: > > 0 4 * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql > > that'll run at 4am every day. When I run the command in my shell (not in Cron),

Re: [GENERAL] Locking Tables & Backup Inquiry

On Wed, Dec 14, 2011 at 1:15 PM, Andy Colson wrote: > Yep, you simply cron a pg_dump.  (dumpall if you want users/roles and all > databases).  No locking needed. So how would one put this in cron if I wanted to run this everyday? 0 * * * * /usr/bin/pg_dumpall > pg_dumpall.$DATE.sql Will that wo

[GENERAL] Locking Tables & Backup Inquiry

I'm wanted to find out why is it recommended or even an option to lock tables during a backup of a database? I've never experimented with database backups so I'm only guessing it locks / freezes the data so no changes can be made while the backup is in process, correct? Just curious and wasn't abl

Re: [GENERAL] Server/Data Migration Advice

On Mon, Dec 12, 2011 at 12:23 PM, Andreas Kretschmer wrote: > - you should use 9.1.2, not 9.1.1 ;-) I don't think it's available yet in Debian repositories. I can only use whatever packages they've compiled in their repositories. > - use the pg_dumpall from the new version to make the dump, for

[GENERAL] Server/Data Migration Advice

I've finally received my new virtual database server (Debian Linux) & this weekend I'll be rolling my new PostgreSQL server online. My question is I've never migrated production data from 8.4.8 to 9.1.1. I would like to find out from the community what exactly is the recommended process in moving m

[GENERAL] Create Tables As Specific Role

I'm installing a calendar application called MRBS. The installation instructions require I create a role and database specifically for this web application. I'm currenlt logged in as my user account 'carlos' which is a superuser. postgres=# SELECT current_user; current_user -- carlos

Re: [GENERAL] Replication Across Two Servers?

On Fri, Nov 4, 2011 at 11:56 AM, Prashant Bharucha < prashantbharu...@yahoo.ca> wrote: > Hi Carlos > > Use Slony "master to multiple slaves" replication system for PostgreSQL > supporting cascading (*e.g.* - a node can > feed another node which feeds another node...) a

Re: [GENERAL] Replication Across Two Servers?

On Fri, Nov 4, 2011 at 11:52 AM, Brandon Phelps wrote: > Carlos, > > Streaming replication was introduced in PostgreSQL 9.0 and should do what > you want. > > http://wiki.postgresql.org/wiki/Streaming_Replication Oh great! I didn't see that in the 8.4 manual since that is what Debian 6 has as the

[GENERAL] Replication Across Two Servers?

We had a 8.4.8 production server of PostgreSQL on a Dell blade server which ran for 3 years fine. The server housed all our database needs perfectly but sadly the entire machine died. The drives were dead and the motherboard was fried but we did have daily full backups of the entire machine. Today

Re: [GENERAL] Postgres Account Inherit Question

On Fri, Oct 14, 2011 at 12:44 PM, Julien Rouhaud wrote: > > Hi > Did you check for a .pgpass file ? I'm assuming you're talking about a hidden file in my Linux shell for the 'postgres' user. I don't see one anywhere. I just had a .psql_history file which I removed. On Fri, Oct 14, 2011 at 1:31 P

[GENERAL] Confused About pg_* Tables

I'm confused about how I'm able to access the following pg_* tables regardless of connected database. I thought these tables were hidden or stored in the 'postgres' database but I'm still able to access this data regardless of which database I'm connected to: Code: zoo=# SELECT * FROM pg_user; u

[GENERAL] Postgres Account Inherit Question

I've configured my 'pg_hba.conf' file to look as follows: # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall all 192.168.0.0/2

Re: [GENERAL] Backup Database Question

On Thu, Oct 6, 2011 at 3:12 PM, John R Pierce wrote: >   /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz Thanks John. I've never written a script so do I just use 'Vim' to open a new file and just paste the following line? #!/bin/bash /usr/bin/pg_dumpall | gzip > /var/db_backup/

[GENERAL] Tuning Variables For PostgreSQL

I read all the time that most DBA's are required or should tune their DBMS which obviously in my case would be PostgreSQL but I'm curious what exactly is involved when tuning a DBMS like PostgreSQL. What are some of the basic functions involved when tuning? Are there specific things I should tweak

Re: [GENERAL] Backup Database Question

On Thu, Oct 6, 2011 at 11:19 AM, Tom Lane wrote: > Use pg_dumpall.  The extra time to dump the user and database > definitions is unlikely to be noticeable, and if push comes to shove > you'll be glad you had them. Yes I agree but I didn't know enough about PostgreSQL to make that determination.

[GENERAL] Backup Database Question

So I'm looking to start regularly backing up my production database at work. I'm tired of doing it manually every day before I go home. I use the built in 'pg_dump' or 'pg_dumpall' utilities however I don't know which is more beneficial for a nightly backup. Perhaps I should be using the 'pg_dumpal

[GENERAL] PostgreSQL Upgrade Procedure

I've had PG 9.0 installed and working fine however it's Friday and I'm running updates on the server & see that 9.1 is available. I know when I upgrade, I will now have two instances of PostgreSQL installed under /etc/postgresql: Code: slave:~# cd /etc/postgresql slave:/etc/postgresql# ls -l tota

Re: [GENERAL] PostgreSQL 9.0 users

I don't have bench marks but upgraded from 8.4 to 9.0 and it works perfect. No performance issues or problems but I highly recommend 9.0.4! On Jun 11, 2011 6:56 AM, "Zhidong She" wrote: > Hi all, > > Could you please give us some typical users that already upgraded to > version 9.0? > We have a de

Re: [GENERAL] Unable To Change Data Type

On Fri, Jun 10, 2011 at 1:57 PM, Bill Moran wrote: > I don't think ALTER COLUMN TYPE will implicitly convert from varchar > to INT. > > Try: > ALTER TABLE reference >  ALTER COLUMN color >    TYPE INT >    USING CAST(color AS INT); Your command suggestion worked perfect but can you explain why yo

[GENERAL] Unable To Change Data Type

For some reason I'm unable to change a column's TYPE from VARCHAR(20) to INTERGER or SMALLINT. I'm required to note the manufactures color code (value = 198) in the table data but keep getting this error and I don't understand why: The error I'm recieving is: ERROR: column "color" cannot be cast

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 2:32 PM, Susan Cassidy wrote: > Don't forget to use setval to set the current value of the sequence to the > highest number used in the data already, so that the next insertion uses a > new, unused value. Doesn't the SERIAL shortcut automatically do this on the fly? How

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell wrote: > That's because of what I just mentioned above. :-) It's not a type: it's > just a shortcut. What you need to do instead is something like this: > >  -- Create the sequence. >  create sequence users_id_seq; > >  -- Tell the column to pull

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 12:57 PM, Carlos Mennens wrote: > On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell wrote: >> Yes, that's exactly right - SERIAL does it all for you. The mistake some >> people make, on the other hand, is thinking that SERIAL is a type in its ow

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell wrote: > Yes, that's exactly right - SERIAL does it all for you. The mistake some > people make, on the other hand, is thinking that SERIAL is a type in its own > right - it's not, it just does all those steps automatically. This information you

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 12:12 PM, Raymond O'Donnell wrote: > Well, the SERIAL pseudo-type creates the sequence, associates it with the > column, and sets a DEFAULT on the column which executes the nextval() > function on the sequence - all in one fell swoop. Read all about it here: > > http://www.

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 11:22 AM, Jaime Casanova wrote: > in postgres is as easy as > > CREATE TABLE test( >  id SERIAL PRIMARY KEY); > > hey! it's even less keystrokes! I don't understand how this command above is associated with being able to auto increment the 'id' column. Sorry I'm still lear

Re: [GENERAL] Remove Modifiers on Table

On Mon, May 16, 2011 at 4:58 PM, Bosco Rama wrote: > If you are truly intent on removing the sequence you'll need to do the > following: > >   alter sequence users_seq_id owned by NONE >   alter table users alter column id drop default >   drop sequence users_seq_id Yes that worked perfect! I'm j

[GENERAL] Remove Modifiers on Table

I created a modifier for auto incrementing my primary key as follows: records=# \d users Table "public.users" Column | Type | Modifiers +---+ id

Re: [GENERAL] Switching Database Engines

On Sat, Apr 30, 2011 at 4:29 AM, Greg Smith wrote: > I wouldn't fight with this too much though.  Unless you have some really > customized stuff in your wiki, there really is nothing wrong with the idea > of dumping everything into XML, creating a blank PostgreSQL-backed MediaWiki > install, then

Re: [GENERAL] Switching Database Engines

It seems that the 'mysql2postgres.pl' tool has instructions embedded into the file so I ran the command as instructed to take the output file and insert it into my PostgreSQL server and got the following error message: $ psql -p 5432 -h db1 -U wiki -f mediawiki_upgrade.pg Password for user wiki: B

Re: [GENERAL] Switching Database Engines

I was able to export the Wiki database into a single file using the conversion tool mentioned previously. root@ideweb1 postgres]# ./mediawiki_mysql2postgres.pl --db=wiki --user=mediawiki --pass=** Writing file "mediawiki_upgrade.pg" As you can see above that generated a new file in my

Re: [GENERAL] Switching Database Engines

On Tue, Apr 26, 2011 at 4:34 PM, Alban Hertroys wrote: > I don't know much about mediawiki (except for how to use it), but it's not > unusual for modern web-apps to have some functionality to dump their contents > in a consistently formatted file (often XML) that it can subsequently import > in

Re: [GENERAL] Switching Database Engines

On Tue, Apr 26, 2011 at 3:06 PM, Greg Sabino Mullane wrote: > Correct. Keep in mind I don't think the XML route will convert the users > table, just the wiki data itself. As someone else mentioned, the > wiki itself will work fine, but support for any MediaWiki extensions > is hit or miss because

Re: [GENERAL] Switching Database Engines

On Tue, Apr 26, 2011 at 1:44 PM, Greg Sabino Mullane wrote: > In general, yes. For your specific use case, it might be best to use > MediaWiki's XML dump and restore. You could also use the conversion > script that comes with MediaWiki, at: > > maintenance/postgres/mediawiki_mysql2postgres.pl > >

[GENERAL] Switching Database Engines

We've been using a Wiki server at the office for years. It was originally configured to use MySQL and finally after 8+ years we're moving the Wiki to a new platform of hardware. My question is the Wiki software (MediaWiki) is the only thing still tied to and using MySQL which we want to decommissio

Re: [GENERAL] Rename or Re-Create Constraints?

On Sat, Apr 9, 2011 at 12:58 PM, Tom Lane wrote: > ALTER INDEX accounts_pkey RENAME TO whatever > > On very old versions of PG you may have to spell that "ALTER TABLE" > instead of "ALTER INDEX", but it's the same thing either way. Thank you so much for clearing that up for me Tom! I just couldn'

Re: [GENERAL] Rename or Re-Create Constraints?

On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: > I believe you can rename the underlying indexes and the constraints will > follow them.  (This works in HEAD anyway, not sure how far back.) Below is my table: inkpress=# \d marketing Table "public.marketing" Column | Type

Re: [GENERAL] Rename or Re-Create Constraints?

On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: > I believe you can rename the underlying indexes and the constraints will > follow them.  (This works in HEAD anyway, not sure how far back.) I'm sorry but I don't understand what that means or how to relate that to a SQL command to rename the cons

[GENERAL] Rename or Re-Create Constraints?

I've searched and really can't find a definitive example or someone renaming a constraint. I renamed a table yesterday and noticed that the constraint name was still named the old table name: inkpress=# ALTER TABLE accounts RENAME TO fashion; ALTER TABLE inkpress=# \d fashion Table "pu

Re: [GENERAL] Changed SSL Certificates

On Fri, Apr 8, 2011 at 2:01 PM, Adrian Klaver wrote: > Per here: > http://www.postgresql.org/docs/8.4/static/ssl-tcp.html > File    Contents        Effect > server.crt      server certificate      requested by client > server.key      server private key      proves server certificate sent by > own

Re: [GENERAL] Changed SSL Certificates

On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz wrote: > Hi, > When linking to the certificate and key you should specify the full path. > ln -s /etc/ssl/certs/db1_ssl.crt      /full/path/to/db1_ssl.crt > ln -s /etc/ssl/private/db1_ssl.key   /full/path/to/db1_ssl.key Thanks for the quick reply Diego

[GENERAL] Changed SSL Certificates

I had self signed SSL certificates on my database server but since then removed them and received updated certificates from the security team. I removed (backedup) the old server.crt & server.key and now have db1_ssl.crt & db1_ssl.key in the identical location as the old SSL certificates. I then we

Re: [GENERAL] Auto Adjust Age

On Wed, Apr 6, 2011 at 11:24 AM, JC de Villa wrote: > Theres also the age() function > > SELECT age(dob); > > Should give you > >           age > - >  31 years 5 mons 17 days > > If you want to be really exact about it. :) That worked awesome too! ide=# SELECT age(dob

Re: [GENERAL] Auto Adjust Age

On Wed, Apr 6, 2011 at 11:20 AM, Andrew Sullivan wrote: > Why do you have the age stored at all?  When you SELECT from the table > and want someone's age, just do > >    SELECT [. . .], extract('years' from age(CURRENT_TIMESTAMP,dob)) as age >           . . . FROM users . . . > > By and large, it'

[GENERAL] Auto Adjust Age

I've only been using PostgreSQL since Oct 2010 and it's my first experience with SQL or any ORDBMS. I've searched on the web and been creating my own database users, databases, tables from scratch which has been interesting to say the least but now I would like to know if this is possible in SQL or

Re: [GENERAL] PostgreSQL ODBC Driver Help

On Tue, Feb 15, 2011 at 12:46 PM, A.M. wrote: > I googled "connect excel postgresql" and found this: > > http://port25.technet.com/videos/research/excelopendbprimer.pdf > > which seems to take one through all the steps. I have been using that actual .PDF as a guide and I guess it's different on O

[GENERAL] PostgreSQL ODBC Driver Help

I'm trying to figure out how I can have users in the office connect their Microsoft Office 2007 clients to our company database server running PostgreSQL 8.4.7. I've configured PostgreSQL to accept incoming connections and allow users to login however I read that I need to have each client install

[GENERAL] Storing Media Types

I've created a basic table called 'employees' & I've been asked to store a profile photo of all employees. I've looked on Google and the 9.0 documentation but can't find any clear instructions on how I would be to insert photo's stored in a local directory on the server (/var/lib/postgres/data/medi

[GENERAL] No Password Access

Today on a new PostgreSQL 9.0.3 server I created a new user: CREATE ROLE carlos LOGIN CREATEDB CREATEROLE; CREATE ROLE I then set a password and comment on the user: ALTER ROLE carlos WITH PASSWORD 'letmein'; ALTER ROLE COMMENT ON ROLE carlos IS 'Database Administrator'; COMMENT So I now try t

Re: [GENERAL] Additional Grants To SuperUser?

On Fri, Feb 4, 2011 at 5:08 PM, Dmitriy Igrishin wrote: > These all (SUPERUSER, CREATEDB, SUPERUSER) are role attributes. > By performing ALTER ROLE postgres NOSUPERUSER it is possible to > turn role with a superuser status into a role that just can create databases > and manage roles (admin, but

Re: [GENERAL] Additional Grants To SuperUser?

On Fri, Feb 4, 2011 at 2:18 PM, David Johnston wrote: > Not to be smart about it but you could just logon as carlos (or a different > superuser you create for this purpose) and issue "Create Database xxx" and > "Create Role xxx" statements and see whether they work.  A superuser should > (imo) be

[GENERAL] Additional Grants To SuperUser?

I created a role named 'carlos' which is my current user account with 'superuser' grants but my question is when I look at 'postgres' account, he has additional grants that I don't understand. List of roles Role name | Attributes | Member of ---+-+--- car

[GENERAL] Remove Role Membership

I've been searching the documentation and I've tried ALTER ROLE, REVOKE, etc etc etc & can't seem to find anything that shows me how to remove membership roles from a particular user / role. I've granted a user name 'david' a member of 'finance' role but how do I remove the role membership from 'da

Re: [GENERAL] Database Design Question

Thanks for all the suggestions and everyone appears to agree that if the applications don't need to share data, then I should split them up into separate database and nothing more. I appreciate your input and explanations as well. -Carlos -- Sent via pgsql-general mailing list (pgsql-general@po

[GENERAL] Database Design Question

I was sitting down thinking the other day about when is it good to generate a new database or just use an existing one. For example, lets say my company name is called 'databasedummy.org' and I have a database called 'dbdummy'. Now I need PostgreSQL to manage several applications for my company: -

Re: [GENERAL] linux server configuration

On Thu, Jan 6, 2011 at 6:20 AM, Sim Zacks wrote: > We are about to build a new database server, our plan is to use Debian. > > Is there documentation of recommended server configurations for Linux, such > as kernel parameters, preferred file system, etc that work best with > postgresql? > > I'm no

[GENERAL] Rename Schema Removes Unique Constraints?

I decided last night to rename my 'public' schema (Not sure of that's a good / bad idea) since I'm still learning about how schema's work on PostgreSQL. My question is: 1. If I have a constraint (specifically 'unique') on a specific table, when I rename the public schema, does that impact my ''uni

[GENERAL] Understanding Roles & Grant Options

I've been reading the documentation and I'm trying to understand what 'GRANT' options make up a 'superuser' in PostgreSQL. I've got my account which is 'carlos' and then I have an account called 'jason'. Can someone please explain the difference between the two roles: postgres=# \dg L

Re: [GENERAL] Postgres forums ... take 2

ld like to see this grow into a full dedicated "official" web forums. -Carlos Mennens -- 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] Role Membership

On Mon, Dec 20, 2010 at 1:32 PM, Scott Marlowe wrote: > No user, no group, they're al roles.  Roles are both / either. Ah now I understand. Thank you! > You grant them that: > > grant rolename to username; > > Then you only ever have to grant / revoke a role to change > permissions, no need to d

Re: [GENERAL] Role Membership

On Mon, Dec 20, 2010 at 12:05 PM, Scott Marlowe wrote: > Odd, mine does.  Got a complete example of creating a role and not seeing it? > > Here's mine: > smarlowe=# create role stans; > CREATE ROLE > smarlowe=# \dg >            List of roles >  Role name |  Attributes  | Member of > ---+--

Re: [GENERAL] Role Membership

On Mon, Dec 20, 2010 at 10:41 AM, Adrian Klaver wrote: > Roles = users/groups. In older versions there where users and groups, that has > been consolidated into the concept of a role. If it makes it easier I use the > concept of roles with login privileges as a users and roles without as groups.

[GENERAL] Role Membership

I was looking at my users and realized none of my users are members of a specific group or role. Not sure if there's a difference between the two (role / group) in PostgreSQL, is there? easports=# \du List of roles Role name |Attributes | Member of -

[GENERAL] What Programs Do You Use For PG?

I'm just wondering what programs you guys / girls are using PostgreSQL for. So far I've installed PG 9 on my Debian Linux server and manually created all my databases, schema's, and tables for my personal email / address book. It's very basic and small but I was wondering if you guys know of any pr

Re: [GENERAL] Understanding Schema's

On Tue, Dec 14, 2010 at 7:17 PM, Joshua D. Drake wrote: > You can cross query a schema but not a database. > > So you can create: > > create table fire.foo() > create table ice.foo() > > And they are isolated from each other physically and logically but you > can query them both: > > SELECT fire.*

[GENERAL] Understanding Schema's

I've recently switched from MySQL & have read the documentation for 'schema's' however I guess I'm just not at that level or really daft when it comes to database design. http://www.postgresql.org/docs/current/static/ddl-schemas.html I'm trying to understand the relation between actual databases

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

On Tue, Nov 2, 2010 at 10:53 AM, Steve Clark wrote: > mv /var/lib/postgres/data  /var/lib/postgres/data.old Before I move or rename '/var/lib/postgres/data', what version of PostgreSQL should I be at? 8.4 or 9.0? > You will then have to do an initdb to create the basic 9.x databases. > You can t

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

So I am still in the dark about the entire upgrade or step up process from 8.4.4-6 to 9.0.1-2. I have my 4 databases all backed up which I did when my server was 8.4.4-6 using the 'pg_dump' utility. That worked fine. So after I backed up my databases, I then upgraded the daemon to 9.0.1-2 and from

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

On Mon, Nov 1, 2010 at 2:27 PM, Steve Crawford wrote: > I'm guessing you are missing an initdb. Move your old data directory > somewhere else for now and do a new initdb so you can start up version 9. When you say 'old data' can you be more specific as to the path and possible files I need to mo

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

On Mon, Nov 1, 2010 at 2:00 PM, Bill Moran wrote: > To clarify my earlier comments, if you're going to use pg_upgrade, you > probably won't need to downgrade to 8.4.  My comments about putting > 8.4 back on would have be necessary if you were going to go the old > dump/restore route. I've already

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

On Mon, Nov 1, 2010 at 1:32 PM, Scott Marlowe wrote: > you would do it with 9.0.x installed, and there should be a program in > one of the 9.0 packages that has pg_upgrade in it. So I have my 8.4.4-6 databases backed up. I don't know if I needed the default 'postgres' database dumped but I did th

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

On Mon, Nov 1, 2010 at 12:52 PM, Richard Broersma wrote: > oops: http://www.postgresql.org/docs/9.0/interactive/pgupgrade.html Thanks for the URL. I will try this but I am confused how to proceed? Can I attempt this with PostgreSQL 9.0.1-2 server installed and the data is still 8.4 or do I need

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

On Mon, Nov 1, 2010 at 12:45 PM, Bill Moran wrote: > I can't speak for Arch Linux' upgrade setup, but going from 8.4 -> 9.0 > requires that the data directory either be dumped/recreated, or ran > through the new upgrade process (which (as yet) I have no experience > with). > > If the Arch Linux st

[GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

I did an upgrade on my database server this past weekend and the database fails to start. I checked /var/log/postgresql and found the reason: [r...@slave ~]# ps aux | grep postgres root 5189 0.0 0.0 8128 956 pts/0S+ 12:28 0:00 grep postgres [r...@slave ~]# /etc/rc.d/postgresql

Re: [GENERAL] Adding a New Column Specifically In a Table

On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens wrote: > OK so I have read the docs and Google to try and find a way to add a > new column to an existing table. My problem is I need this new column > to be created 3rd  rather than just dumping this new column to the end > of my ta

[GENERAL] Adding a New Column Specifically In a Table

OK so I have read the docs and Google to try and find a way to add a new column to an existing table. My problem is I need this new column to be created 3rd rather than just dumping this new column to the end of my table. I can't find anywhere how I can insert my new column as the 3rd table column

  1   2   >