Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?
On Fri, 2007-03-09 at 01:22 +, Bradley Kieser wrote: > I hope that someone has cracked this one because I have run into a brick > wall the entire week and after 3 all-nighters with bad installations, I > would appreciate hearing from others! > > I am looking for a decent OpenSource CRM system that will run with > Postgres. SugarCRM seems to be the most popular but it's MySQL-centric > and its opensource parts are very restricted. > > vTiger is also mySQL-centric. > > I thought that I had a corker of a system with "centricCRM" but when it > came to actually installing it, I am 48 hours down and hacking through > screen after screen of installation errors. Basically, it relies way too > much on ant and Java tools. Nothing against Java but my experience with > ant used for installing PG schemas is a dismal track record of error and > frustration. centric CRM is no exception. Frankly, it just doesn't work > and after trying to hack out the ant into a PG script I have decided to > give it up as a bad job. > > XRMS promises to run on PG but... it doesn't. The core system is fine, > but useless without the plugins. The Plugins are mySQL-specific again, I > spent several all-nighters previously hacking through installation > screens attempting to convert mysql to PG, making software patches... > you get the picture. > > XLSuite looks very promising. Awesome interface, looks great... only > it's just not ready yet. It is a year away from being at full PG > production level. > > Compiere doesn't support PG. > > OpenTAPS the demo won't even work. And it's US-centric whereas we are in > the UK. A pity that it's so very much tied to the US as it could be very > good. > > I have tried numerous other CRMs but all the same - either don't run on > PG, claim to but in reality don't or are simply pre-Alpha and not ready > for production use. > > So if anyone has actually cracked this, please let me know! I really > need a good CRM. > > It has to be OpenSource, not just out of principle, but we need to > integrate it into an existing business with established inhouse software > so we need to be able to customise the code. my experience with CRM stuff is that the general CRM application never does what you want and you are going to have to hack it no matter what. If you are comfortable with going PHP, you just download sugarcrm or vtiger or whatever comes closest to your vision of your needs and hack away from there. Myself, I am very much enthralled with Ruby on Rails and see it as an amazingly rapid development system and have been writing everything from scratch for our non-profit. Craig ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] getting list of tables from command line
I wrote a little script to individually back up table schemas, table data and then vacuum the table and it works nicely but I wanted a way to query a database and get a text file with just the table names and cannot figure out a way to do that. my script looks like this... (all I want is to get a list of the tables into a text file pg_tables) #/bin/sh # DB_NAME=whatever # for i in `cat pg_tables` do pg_dump --username=postgres \ --schema=db --table=$i \ --schema-only \ $DB_NAME > schemas/$i.sql pg_dump --username=postgres \ --schema=db \ --table=$i \ --data-only \ $DB_NAME > data/$i.sql vacuumdb --username=postgres \ --dbname=$DB_NAME \ --table=db.$i \ --verbose \ --full done Is there a way to do that? Craig PS there's a lack of cohesion between various commands such as vacuumdb and pg_dump for things like '--schema' ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] getting list of tables from command line
On Thu, 2007-11-01 at 20:25 +0100, hubert depesz lubaczewski wrote: > On Wed, Oct 31, 2007 at 08:01:41AM -0700, Craig White wrote: > > my script looks like this... > > (all I want is to get a list of the tables into a text file pg_tables) > > everybody else showed some ways, but i'll ask a question: > i hope you're not treating it as a backup? bacause when you do it that > way (on table-by-table basic) the "backup" is practically useless as it > doesn't have internal consistency. > > if you want to make backup, perhaps you can tell us exactly what you > want to do, and why standard pg_dump is not enough. > > if you're fully aware of the issue i pointed - sorry, i'm just trying to > make sure you'll not shoot yourself in a foot. No - thanks...you raised a very good point. First I would like to thank all of those who responded (Reece, T.J., David, Filip, Ron) with great ideas...I learned a lot. I also have pg_dumpall on a weekly basis and pg_dump each database nightly. I learned that a long time ago when I was in early development and migrated my fedora installation which updated postgres and my database no longer worked. I wanted the table separations nightly as insurance from things like bad migrations (ruby on rails which alters the tables via scripting) and the ability to reload the data from a table based on poorly conceived coding (not that I would ever do such a thing), or to make it easier for me to move data from my production database to my development database. I am fortunate that even though I am now past 30 tables in my project (production) and we've been adding data for a year and a half, the total data backup is only 7 Megabytes (/var/lib/pgsql/data is only 132 megabytes) so I can't have too many different backups made nightly via cron scripts at this point. I also turned on auto-vacuum in the preferences but this method seems much more thorough. For anyone interested, this is what I settled upon for my final script (heck, I don't even bother tar/zip the things yet)... *** begin pg_table_dump.scr *** #/bin/sh # # Script to identify tables, backup schema and data separately and # then finally, vacuum each table # DB_NAME=MY_DB BACKUP_PATH=/home/backup/postgres/production MY_SCHEMA=public PG_USER=craig # psql -U $PG_USER \ $DB_NAME \ -c "SELECT tablename FROM pg_tables WHERE \ schemaname = "\'$MY_SCHEMA\'";" | \ grep -v 'tablename' | \ grep -v [\--*] | \ grep -v rows\) > $BACKUP_PATH/pg_tables # for i in `cat $BACKUP_PATH/pg_tables` do pg_dump \ --username=$PG_USER \ --schema=$MY_SCHEMA \ --table=$i \ --schema-only \ $DB_NAME > $BACKUP_PATH/schemas/$i.sql pg_dump \ --username=$PG_USER \ --schema=$MY_SCHEMA \ --table=$i \ --data-only \ $DB_NAME > $BACKUP_PATH/data/$i.sql vacuumdb \ --username=$PG_USER \ --dbname=$DB_NAME \ --table=$MY_SCHEMA.$i \ --verbose \ --full done *** end pg_table_dump.scr *** Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] authentication question
CentOS 4.4 which means postgresql-server-7.4.13-2.RHEL4.1 I'm starting to deal with the notion of allowing other users access (read only) to a db. Experimenting on my own db... hostall main_user 192.168.2.10255.255.255.0 trust hostall all 127.0.0.1 255.255.255.255 trust hostall craig 192.168.2.10255.255.255.255 pam because I want to use LDAP authentication via pam. logs say... Nov 8 20:18:26 srv1 postgresql: Starting postgresql service: succeeded Nov 8 20:18:39 srv1 postgres[21020]: PAM audit_open() failed: Permission denied Nov 8 20:18:39 srv1 postgres[21020]: [2-1] LOG: pam_authenticate failed: System error Nov 8 20:18:39 srv1 postgres[21020]: [3-1] FATAL: PAM authentication failed for user "craig" Below is pam info - if anyone can tell me how I might configure this so I can authenticate via LDAP I would appreciate it. Craig # cat /etc/pam.d/postgresql #%PAM-1.0 auth required pam_stack.so service=system-auth auth required pam_nologin.so accountrequired pam_stack.so service=system-auth password required pam_stack.so service=system-auth sessionrequired pam_stack.so service=system-auth sessionrequired pam_loginuid.so which was cribbed from /etc/pam.d/sshd # cat /etc/pam.d/system-auth #%PAM-1.0 # This file is auto-generated. # User changes will be destroyed the next time authconfig is run. authrequired /lib/security/$ISA/pam_env.so authsufficient/lib/security/$ISA/pam_unix.so likeauth nullok authsufficient/lib/security/$ISA/pam_ldap.so use_first_pass authrequired /lib/security/$ISA/pam_deny.so account required /lib/security/$ISA/pam_unix.so broken_shadow account sufficient/lib/security/$ISA/pam_succeed_if.so uid < 100 quiet account [default=bad success=ok user_unknown=ignore] /lib/security/$ISA/pam_ldap.so account required /lib/security/$ISA/pam_permit.so passwordrequisite /lib/security/$ISA/pam_cracklib.so retry=3 passwordsufficient/lib/security/$ISA/pam_unix.so nullok use_authtok md5 shadow passwordsufficient/lib/security/$ISA/pam_ldap.so use_authtok passwordrequired /lib/security/$ISA/pam_deny.so session required /lib/security/$ISA/pam_limits.so session required /lib/security/$ISA/pam_unix.so session optional /lib/security/$ISA/pam_ldap.so ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] authentication question
On Thu, 2006-11-09 at 12:34 -0300, Alvaro Herrera wrote: > Craig White wrote: > > > logs say... > > Nov 8 20:18:26 srv1 postgresql: Starting postgresql service: succeeded > > Nov 8 20:18:39 srv1 postgres[21020]: PAM audit_open() failed: > > Permission denied > > Nov 8 20:18:39 srv1 postgres[21020]: [2-1] LOG: pam_authenticate > > failed: System error > > Nov 8 20:18:39 srv1 postgres[21020]: [3-1] FATAL: PAM authentication > > failed for user "craig" > > I'm not at all familiar with PAM error message wording, but are you > aware that you must create the user "craig" inside the database _as > well_ as on whatever PAM layer you use? > > The "audit_open(): Permission denied" message sounds like Postgres is > not authorized to consult PAM though. I did create a user 'craig' in postgres but I agree, that isn't the issue at this point. I checked the source rpm to make sure that it was compiled with the pam option and it appears to me that it was. I haven't had to fool too much with pam for authenticating other services so I'm a little bit out of my knowledge base but I know that it was simple to add netatalk into the pam authentication and expected that postgresql would be similar. I have to believe that other people are using pam for authentication because otherwise, you have to have maintain passwords for each user within postgresql itself - which seems unwise for many sites. Still struggling with this... Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] authentication question
On Thu, 2006-11-09 at 11:51 -0500, Tom Lane wrote:
> Craig White <[EMAIL PROTECTED]> writes:
> > I haven't had to fool too much with pam for authenticating other
> > services so I'm a little bit out of my knowledge base but I know that it
> > was simple to add netatalk into the pam authentication and expected that
> > postgresql would be similar.
>
> FWIW, we ship this PAM config file in the Red Hat PG RPMs:
>
> #%PAM-1.0
> auth include system-auth
> account include system-auth
>
> which AFAIR looks about the same as the corresponding files for other
> services. It's installed as /etc/pam.d/postgresql.
that doesn't work at all... /var/log/messages reports...
Nov 9 10:26:33 srv1 postgres[6034]: PAM unable to
dlopen(/lib/security/system-auth)
Nov 9 10:26:33 srv1 postgres[6034]: PAM
[dlerror: /lib/security/system-auth: cannot open shared object file: No
such file or directory]
as for what is included BY Red Hat in their postgresql-server rpm...
# rpm -ql postgresql-server|grep pam
returns nothing which makes me double check the spec file on the RPM
which has...
# grep pam /usr/src/redhat/SPECS/postgresql.spec
#work around the undefined or defined to 1 build 6x interaction with the
pam stuff
%{!?build6x:%define non6xpamdeps 1}
%{?build6x:%define non6xpamdeps 0}
%{!?pam:%define pam 1}
%if %pam
%if %non6xpamdeps
BuildPrereq: pam-devel
%if %pam
--with-pam \
a search of Red Hat's bugzilla shows all postgresql bugs closed and
nothing reporting a problem with pam ;-(
and since it does attempt to call pam (as I am seeing in logs), I am
certain that pam option is compiled.
I'm virtually certain that I am better off pointing
to /etc/pam.d/system-auth which clearly works for sshd logins
>
> I concur with the other response that you need to find out where the
> "Permission denied" failure is coming from. There is no "audit_open"
> in the Postgres sources so it sounds like an internal failure in the PAM
> libraries. If nothing else comes to mind, try strace'ing the postmaster
> to see what kernel call draws that failure.
pretty short strace but I can't see anything that jumps at me and says
aha...
# strace -p 3267
Process 3267 attached - interrupt to quit
select(6, [3 5], NULL, NULL, {95, 566000}) = 1 (in [3], left {88,
881000})
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN
RT_1], NULL, 8) = 0
accept(3, {sa_family=AF_INET, sin_port=htons(56844),
sin_addr=inet_addr("192.168.2.10")}, [16]) = 9
getsockname(9, {sa_family=AF_INET, sin_port=htons(5432),
sin_addr=inet_addr("192.168.2.1")}, [16]) = 0
setsockopt(9, SOL_TCP, TCP_NODELAY, [1], 4) = 0
setsockopt(9, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|
SIGCHLD, child_tidptr=0xb7f2e708) = 5921
close(9)= 0
time(NULL) = 1163093004
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
select(6, [3 5], NULL, NULL, {89, 0}) = ? ERESTARTNOHAND (To be
restarted)
--- SIGCHLD (Child exited) @ 0 (0) ---
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN
RT_1], NULL, 8) = 0
waitpid(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG) = 5921
send(6, "\2\0\0\0\30\0\0\0\0\0\0\0!\27\0\0\0\0\0\0\0\0\0\0", 24, 0) = 24
waitpid(-1, 0xbfecf5fc, WNOHANG)= 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN
RT_1], NULL, 8) = 0
time(NULL) = 1163093004
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
select(6, [3 5], NULL, NULL, {89, 0}
Process 3267 detached
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] authentication question
On Thu, 2006-11-09 at 16:34 -0300, Alvaro Herrera wrote: > Tom Lane wrote: > > Craig White <[EMAIL PROTECTED]> writes: > > > I haven't had to fool too much with pam for authenticating other > > > services so I'm a little bit out of my knowledge base but I know that it > > > was simple to add netatalk into the pam authentication and expected that > > > postgresql would be similar. > > > > FWIW, we ship this PAM config file in the Red Hat PG RPMs: > > > > #%PAM-1.0 > > authinclude system-auth > > account include system-auth > > > > which AFAIR looks about the same as the corresponding files for other > > services. It's installed as /etc/pam.d/postgresql. > > For this to work you need a system-auth file in /etc/pam.d, which would > have lines for auth/account/required etc, and not just "includes". > > PAM seems to be another area on which Linux distributors have been > diverging wildly for a long time; for example here on Debian the include > lines look like > > authrequisite pam_nologin.so > authrequiredpam_env.so > @include common-auth > @include common-account > session requiredpam_limits.so > > so I doubt one distro's config files are applicable to any other. and I'm on a Red Hat system which obviously Tom is familiar with since he is the packager for RH / postgres but I don't think that is the issue but I have adopted his pam file. Thanks Craig ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SOLVED] Re: [GENERAL] authentication question
Just in case others follow in my footsteps - this may prove to be helpful. Summary of problem: CentOS 4.4 - SELinux enabled - authorizing pam based users ### Created file /etc/pam.d/postgresql (I'm using LDAP) [*] # cat /etc/pam.d/postgresql #%PAM-1.0 auth required pam_stack.so service=system-auth auth required pam_nologin.so accountrequired pam_stack.so service=system-auth password required pam_stack.so service=system-auth sessionrequired pam_stack.so service=system-auth sessionrequired pam_loginuid.so ### Set SELinux security contexts for this file # chcon -u system_u -r object_r /etc/pam.d/postgresql ### Already had installed rpm selinux-policy-targeted-sources ### You will need this package ### ### Added to file /etc/selinux/src/targeted/policy/domains/local.te # cat /etc/selinux/targeted/src/policy/domains/local.te # postgres/pam allow postgresql_t self:netlink_audit_socket create; allow postgresql_t self:netlink_audit_socket nlmsg_relay; allow postgresql_t self:netlink_audit_socket read; allow postgresql_t self:netlink_audit_socket write; allow postgresql_t var_lib_t:file read; ### the last line of the changes to local.te were necessary only for ### postgresql user to be able to read /var/lib/pgsql/.ldaprc ### ### now load this new policy into selinux # cd /etc/selinux/targeted/src/policy # make reload Now, I am able to log in as a user from LDAP - with the obvious provisions that the user is a user in postgres (password not needed since that is from LDAP), and pg_hba.conf is properly configured. [*] Tom's suggestion for /etc/pam.d/postgresql file #%PAM-1.0 authrequiredpam_stack.so service=system-auth account requiredpam_stack.so service=system-auth Thanks Tom/Alvaro Craig ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Fri, 2007-11-09 at 23:55 -0500, Mark Niedzielski wrote: > Our developers run on MacBook Pros w/ 2G memory and our production > hardware is dual dual-Core Opterons w/ 8G memory running CentOS 5. The > Macs perform common and complex Postgres operations in about half the > time of our unloaded production hardware. We've compared configurations > and the production hardware is running a much bigger configuration and > faster disk. > > What are we missing? Is there a trick to making AMDs perform? Does > Linux suck compared to BSD? that was an awful lot of discussion without any empirical evidence to support the original claim. my understanding was that the lack of threading on OSX made it especially poor for a DB server (but if I recall correctly, that information was on MySQL). Do I smell a plant? Craig ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Mon, 2007-11-26 at 17:37 -0600, Wes wrote: > On 11/13/07 10:02 AM, "Scott Ribe" <[EMAIL PROTECTED]> wrote: > > > What you're referring to must be that the kernel was essentially > > single-threaded, with a single "kernel-funnel" lock. (Because the OS > > certainly supported threads, and it was certainly possible to write > > highly-threaded applications, and I don't know of any performance problems > > with threaded applications.) > > > > This has been getting progressively better, with each release adding more > > in-kernel concurrency. Which means that 10.5 probably obsoletes all prior > > postgres benchmarks on OS X. > > While I've never seen this documented anywhere, it empirically looks like > 10.5 also (finally) adds CPU affinity to better utilize instruction caching. > On a dual CPU system under 10.4, one CPU bound process would use two CPU's > at 50%. Under 10.5 it uses one CPU at 100%. > > I never saw any resolution to this thread - were the original tests on the > Opteron and OS X identical, or were they two different workloads? resolution? http://archives.postgresql.org/pgsql-general/2007-11/msg00946.php conclusion? Mac was still pretty slow in comparison Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Setting up phppgadmin under https/ssl (Apache)
On Sun, 2008-05-18 at 23:33 -0700, [EMAIL PROTECTED] wrote: > Wow...so does no one use phppgadmin on servers that they are connected > to via the internet? Or if you do, how do you go about securing it so > that no one snoops your password? > > Thanks > > > On May 16, 12:34 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > wrote: > > Hi... > > > > This is as much an apache question as anything else, but I think it's > > appropriate here. > > > > I've been using phppgadmin on my local machine. Now I've installed it > > on a remote server running Ubuntu lts 8.04. > > > > I figured I'd try and put it under ssl/https under Apache (mod_ssl). > > I've created a test certificate, but I'm having trouble figuring out > > exactly how to get phppgadmin working under SSL. Can someone step me > > through the process? I wouldn't recommend making phppgadmin available from the Internet in any form but... SSLRequireSSL would require SSL to access...adjust paths to suit. I also would require user authentication to access the path (I use mod_authz_ldap) so that the user accessing it is logged and of course, then the security aspects of postgres would then apply too. Craig -- 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] Postgres 8.3.x installation on Fedora 9 system
On Thu, 2008-08-07 at 17:24 -0400, Jack Orenstein wrote: > Pardon a dumb question. Installing Postgres 7.x on FC4-6, I would install a > large set of RPMs, these I think: > > postgresql > postgresql-contrib > postgresql-devel > postgresql-jdbc > postgresql-libs > postgresql-python > postgresql-server > > I then had everything I need to run postgres and access it from Java and > Python > (through the pygresql driver). > > I'm now trying to move to 8.3.x on Fedora 9. Using pgdg-83-fedora.repo, yum > finds two RPMs, postgresql-8.3.3-1PGDG.f9.i386 and > postgresql-libs-8.3.3-1PGDG.f9.i386. I checked postgresql.org, but the > fedora 9 > directories are empty, (unlike the f7 and f8 directories). > > I know that the JDBC driver is a separate project, but I'm having trouble > getting python access to work. I downloaded PyGreSQL-3.8.tgz, but cannot > install it because it relies on pg_config, which is not present the the 8.3.3 > RPMs I installed. > > Am I just on the wrong path here, starting with the postgresql and > postgresql-libs RPMs? Fedora 9 has it's own postgres 8.3.3 packages as part of base... # rpm -qa|grep postgres mono-data-postgresql-1.9.1-2.fc9.i386 postgresql-odbc-08.03.0100-1.fc9.i386 postgresql-server-8.3.3-2.fc9.i386 postgresql-python-8.3.3-2.fc9.i386 postgresql-devel-8.3.3-2.fc9.i386 postgresql-8.3.3-2.fc9.i386 postgresql-libs-8.3.3-2.fc9.i386 Not sure why you feel the need to go to a 3rd party Craig -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dumb question
How do I change the owner of a schema? ALTER SCHEMA "public" OWNER to "some_user"; #? Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] dumb question
On Mon, 2006-02-13 at 11:07 +0900, Michael Glaesemann wrote: > On Feb 13, 2006, at 10:29 , Craig White wrote: > > > How do I change the owner of a schema? > > > > ALTER SCHEMA "public" OWNER to "some_user"; #? > > http://www.postgresql.org/docs/current/interactive/sql-alterschema.html > > The docs explain this very situation. HTML documentation ships with > the PostgreSQL distribution and can also be found online. seeing as how the above line seems to me to be exactly like the page that you just referred me to, I have included some clips of my terminal transactions because quite clearly I am too stupid to understand this... th-db_test=> ALTER SCHEMA "public" OWNER TO tobyhouse; ERROR: syntax error at or near "OWNER" at character 23 th-db_test=> ALTER SCHEMA public OWNER TO tobyhouse; ERROR: syntax error at or near "OWNER" at character 21 # rpm -q postgresql-server postgresql-server-7.4.8-1.RHEL4.1 Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] dumb question
On Mon, 2006-02-13 at 11:39 +0900, Michael Glaesemann wrote: > On Feb 13, 2006, at 11:27 , Craig White wrote: > > > th-db_test=> ALTER SCHEMA public OWNER TO tobyhouse; > > ERROR: syntax error at or near "OWNER" at character 21 > > > > # rpm -q postgresql-server > > postgresql-server-7.4.8-1.RHEL4.1 > > The docs I referred you to are for the current release. For earlier > releases, such as 7.4, you need to refer to the appropriate docs, > such as: > > http://www.postgresql.org/docs/7.4/interactive/sql-alterschema.html > > It appears that the ability to assign a new owner to a schema via > ALTER SCHEMA was introduced some time after 7.4. > > You may be able to munge the system tables, but you may want to > consider upgrading your PostgreSQL server installation. seems to be a lot of work just for this...the problem is trying to use rubyonrails and their 'migrations' which cause this... $ rake clone_structure_to_test --trace (in /home/craig/ruby-db/th-db) ** Invoke clone_structure_to_test (first_time) ** Invoke db_structure_dump (first_time) ** Invoke environment (first_time) ** Execute environment ** Execute db_structure_dump ** Invoke purge_test_database (first_time) ** Invoke environment ** Execute purge_test_database ** Execute clone_structure_to_test psql:db/development_structure.sql:28: NOTICE: CREATE TABLE will create implicit sequence "case_managers_id_seq" for "serial" column "case_managers.id" psql:db/development_structure.sql:57: NOTICE: CREATE TABLE will create implicit sequence "placements_id_seq" for "serial" column "placements.id" psql:db/development_structure.sql:70: NOTICE: CREATE TABLE will create implicit sequence "referral_notes_id_seq" for "serial" column "referral_notes.id" psql:db/development_structure.sql:86: NOTICE: CREATE TABLE will create implicit sequence "clients_id_seq" for "serial" column "clients.id" psql:db/development_structure.sql:103: NOTICE: CREATE TABLE will create implicit sequence "facilities_id_seq" for "serial" column "facilities.id" psql:db/development_structure.sql:122: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "case_managers_pkey" for table "case_managers" psql:db/development_structure.sql:131: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "placements_pkey" for table "placements" psql:db/development_structure.sql:140: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "referral_notes_pkey" for table "referral_notes" psql:db/development_structure.sql:149: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "clients_pkey" for table "clients" psql:db/development_structure.sql:158: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "facilities_pkey" for table "facilities" psql:db/development_structure.sql:211: ERROR: must be owner of schema public So how can I just 'munge' the system tables? I have granted all privileges to this user. Thanks Craig ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to import a CSV file (originally from Excel)
On Tue, 2006-04-11 at 23:13 +0200, Magnus Hagander wrote: > > Hello, > > > > I am trying to import an Excel file in my database (8.0.7). I > > think I am supposed to create an CSV file out of my Excel > > file and somehow, feed the database with it. My pronblem is > > that I don't really know how to do it... :( Can anyone give > > me a hint about how to do that? > > One last "detail", the Excel files contains roughly 45.000 > > lines and 15 columns. So, I need a fast and efficient method. > > Hi! > > Is your problem with how to generate the CSV file from Excel, or with > how to import it into PostgreSQL? > > For generation in Excel, you can just use File->Save, and select CSV as > the format. You can use either CSV or tab delimited, really. > > Then to get it into postgresql, use the \copy command in psql (I'm > assuming your client is on windows, since you're using Excel. \copy will > run the process from the client, and will load it into your sever > regardless of platform). You can specify which delimiter to use there, > etc. From the example below, I'd guess you want something along the line > of: > > \copy "Flight Traffic" from yourfile.csv delimiter as ',' csv quote as > '"' > > (might need some adaption, of course) > > > Loading 45,000 lines is trivial for copy, it shouldn't take noticable > time at all. along these lines - can I do something similar (CSV file) but 'update' 2 or 3 columns? Craig ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to import a CSV file (originally from Excel)
On Wed, 2006-04-12 at 17:57 +0200, Magnus Hagander wrote: > > > \copy "Flight Traffic" from yourfile.csv delimiter as ',' > > csv quote as > > > '"' > > > > > > (might need some adaption, of course) > > > > > > > > > Loading 45,000 lines is trivial for copy, it shouldn't take > > noticable > > > time at all. > > > > along these lines - can I do something similar (CSV file) but > > 'update' 2 or 3 columns? > > I'd use COPY to a temp table, then run a normal UPDATE on that. is that a pg 8.x thing? I'm using 7.4.x (RHEL) th-db_development=# \copy "clients_temp" from representatives.csv delimiter as ',' csv quote as '"' ERROR: syntax error at or near "CSV" at character 53 LINE 1: ...PY "clients_temp" FROM STDIN USING DELIMITERS ',' CSV QUOTE ... ^ \copy: ERROR: syntax error at or near "CSV" at character 53 Craig ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] importing data
I am getting an error when I import, invalid input syntax for type boolean - which is empty. Is there any way around this of must I necessarily have \N ? Craig ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] command line
strugging to import I connect and am on the command line but I don't think I am in the right schema. I have looked through the /h /? but can't seem to figure out how to change to database: th-db schema: db version: # rpm -q postgresql postgresql-7.4.8-1.RHEL4.1 Craig ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
