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] 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] 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] 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

[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

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

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

[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

[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] 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

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] 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] 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'

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

[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] 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

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

[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] 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

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 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'

[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] 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 > >

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 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

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

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

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

[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] 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

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 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 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: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 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 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

[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] 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

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

[GENERAL] Post Install / Secure PostgreSQL

I am brand new to PostgreSQL and coming from MySQL. My question is does anyone know after I install PostgreSQL on my Linux server, is there a script that secures the database like MySQL offers in most Linux distributions? I think the script for MySQL is "/usr/bin/mysql_secure_installation". I check

Re: [GENERAL] Post Install / Secure PostgreSQL

On Fri, Sep 10, 2010 at 11:33 AM, Richard Broersma wrote: > I don't believe there is a script like this.  However, I would say > that out of the box, PostgreSQL is so secure that some people cannot > figure out how to log in. :) I agree and I am just now learning this. I can't seem to find out ho

Re: [GENERAL] Post Install / Secure PostgreSQL

Thanks for all the assistance and clarification with my new install of PostgreSQL. I am able to switch users to 'postgres' and verify the default home directory for 'postgres' shell user: [r...@db1 ~]# su - postgres [postg...@db1 ~]$ pwd /var/lib/postgres I am also now able from the documentatio

Re: [GENERAL] Post Install / Secure PostgreSQL

On Mon, Sep 13, 2010 at 1:57 PM, Tom Lane wrote: > It's definitely a good idea not to use a superuser account when you > don't have to; just like you don't use Unix root unless you have to. > You should do your day-to-day database hacking in an ordinary > unprivileged account. When I am logged in

Re: [GENERAL] Post Install / Secure PostgreSQL

On Mon, Sep 13, 2010 at 9:58 PM, Craig Ringer wrote: > craig$ sudo -u postgres psql > postgres=> CREATE USER craig WITH PASSWORD 'somepassword' >           CREATEDB CREATEROLE; > postgres=> CREATE DATABASE craig WITH OWNER craig; > postgres=> \q So I set a Linux shell password on my newly auto cr

Re: [GENERAL] Post Install / Secure PostgreSQL

On Tue, Sep 14, 2010 at 1:52 PM, Richard Broersma > You'd want to use "ALTER USER" > > http://www.postgresql.org/docs/8.4/interactive/sql-alteruser.html > > So: > > ALTER USER carlos WITH ENCRYPTED PASSWORD 'password'; I find it strange when I am logged in as super user 'postgres' and type the ex

[GENERAL] Identify Login User & Permissions

I can't find in the docs or using Google how one can identify which user is currently logged in to psql. I search for the command that if for some reason I forget which user I am logged in as, rather than logging out of PostgreSQL, there has to be a command that shows me who I am and also what kind

Re: [GENERAL] Identify Login User & Permissions

On Tue, Sep 14, 2010 at 4:23 PM, Guillaume Lelarge wrote: > SELECT current_user; Thanks. That worked well: postgres=# SELECT current_user; current_user -- carlos (1 row) Do you know how I can verify what privileges or permissions 'carlos' has granted to him in PostgreSQL? -- Sen

[GENERAL] Table Comments

I saw in the documentation for PostgreSQL that I can add 'comments' to table entries when creating columns: http://www.postgresql.org/docs/8.1/static/tutorial-table.html CREATE TABLE weather ( cityvarchar(80), temp_lo int, -- low temperature temp_hi

Re: [GENERAL] Table Comments

Thank you all for that information. -- 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] Post Install / Secure PostgreSQL

Thanks all for the help! I have a much better understanding now of how user accounts are managed via ident authentication. The only thing I have yet to figure out or understand is how to login to PostgreSQL as my user account but not to any specific database. I understand that when I am logged in

Re: [GENERAL] Post Install / Secure PostgreSQL

On Wed, Sep 15, 2010 at 1:34 PM, John R Pierce wrote: > no, there is no such state in postgres.  you connect and log into a > database.  connecting to a different database requires closing that > connection and opening a new one (which is what the \c command does in > psql). Thanks but then I am

Re: [GENERAL] Post Install / Secure PostgreSQL

On Wed, Sep 15, 2010 at 1:43 PM, John R Pierce wrote: > the 'postgres' database on your system is empty.   this is quite typical, as > that database is simply a convenience for the postgres user to have > something to log into while doing his administrative duties. OK this makes sense and I coul

[GENERAL] Alter Table Command Rearranges Rows

I noticed that my database was in order based on my primary key column called 'id' which when from 1 (first) to 6 (last). Today I had to edit table data which wasn't anything crazy: team=#ALTER users SET name = 'David' WHERE id = '1'; UPDATE 1 Now when I do a 'SELECT * FROM users' command in Post

Re: [GENERAL] Alter Table Command Rearranges Rows

On Fri, Sep 17, 2010 at 4:32 PM, Michael Glaesemann wrote: > This isn't valid syntax: I believe you issued UPDATE users Woops. I did use the UPDATE and not ALTER command. On Fri, Sep 17, 2010 at 4:39 PM, Richard Broersma wrote: > On Fri, Sep 17, 2010 at 1:32 PM, Michael Glaesemann > wrote:

[GENERAL] Alter Table Auto_Increment

I have a table in my database and would like to modify the one column that is already configured to be the PRIMARY KEY but I forgot to set it for AUTO_INCREMENT. For some reason I can't find what the proper command would be in the documentation and my commands from MySQL don't appear to work proper

Re: [GENERAL] Alter Table Auto_Increment

On Mon, Sep 20, 2010 at 10:08 AM, Thom Brown wrote: > CREATE SEQUENCE seq_blades_id; > SELECT setval('seq_blades_id', max(id)) FROM blades; -- set the > sequence's value to the maximum value of "id" > ALTER TABLE blades ALTER COLUMN id SET DEFAULT > nextval('seq_blades_id'); -- make default value

Re: [GENERAL] Alter Table Auto_Increment

On Mon, Sep 20, 2010 at 2:43 PM, A.M. wrote: > DEFAULT Sorry just to be clear you're saying that I need to enter the command as: INSERT INTO table_name DEFAULT VALUES ( 'data', 'data', 'data', 'data', '2010-09-20' ); Or does the 'DEFAULT' value go in ( )? -- Sent via pgsql-general mailing lis

Re: [GENERAL] Alter Table Auto_Increment

Thanks all! I understand the concept now. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Front End Application (OFF-TOPIC)

I am using PostgreSQL to hold my entire I.T. inventory for my company. I am manually entering the data in via hand through the Linux command line and the 'psql' utility which is great for me but other co-workers in I.T. have no understanding of how to view the schema and I wanted to see if anyone h

[GENERAL] No Relations Found Error

I created today a new database and user for my Wiki. Now I created both the db user and actual db as 'carlos' even though the owner of the db 'wiki' is a user named 'wiki'. My user 'carlos' is a superuser however. So I then wanted to view and change some data but I keep getting this error: car...@

Re: [GENERAL] No Relations Found Error

On Thu, Sep 30, 2010 at 3:31 PM, Raymond O'Donnell wrote: > Are the tables in a schema other than "public"? Enter "\dn" at the psql > prompt to see what schemas are there. Perhaps not. I wasn't aware of that. When I created the database before I created and tables, I used the following command:

Re: [GENERAL] No Relations Found Error

On Thu, Sep 30, 2010 at 3:44 PM, Raymond O'Donnell wrote: > On 30/09/2010 20:43, Raymond O'Donnell wrote: > >> http://www.postgresql.org/docs/9.0/static/ddl-schemas.html >> >> Have a look at section 5.3.7 on the search path in particular, as this > > Whoops, sorry - that's 5.7.3. Thank you. I am

Re: [GENERAL] PG website testing

On Mon, Oct 4, 2010 at 2:40 PM, Thom Brown wrote: > Hi all, > > We're currently testing a new javascript change on the PostgreSQL > docs.  This is to make sure monospaced fonts still appear at a > reasonable size between browsers.  I'd appreciate it if some of you > could do some browser testing.

[GENERAL] Understanding PostgreSQL Storage Engines

I know that MySQL uses MyISAM storage engine by default and was just trying to look on Google to try and see if I could understand what storage engine does PostgreSQL use by default when I generate a database / table. Is there some way someone (me) who knows nothing about how a ORDBMS works underst

[GENERAL] NoSQL -vs- SQL

Just wondering how you guys feel about NoSQL and I just wanted to share the following article... http://www.linuxjournal.com/article/10770 Looking to read your feedback and / or opinions. -Carlos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subs

[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

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] 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] 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

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 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 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 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?

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 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

[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] 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] 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

[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 -

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.

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 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] 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

[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

[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] 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

[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

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] 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 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] 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

[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

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] 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] 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

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

[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

[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

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] 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

  1   2   >