Re: [GENERAL] Dump all in several files
On Tue, 15 Mar 2005 17:01:39 +0100, Frederic Massot [EMAIL PROTECTED] wrote: Lonni J Friedman wrote: On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot [EMAIL PROTECTED] wrote: Hi, On the PostgreSQL 6.5 server I use this shell script (see below) for the backup all of the database in several files, one file per database. [...] Maybe i'm just not following you, but why can't you just do: pg_dump $DBNAME $DB_NAME where $DB_NAME is the name of each database on the box? This script is called in a crontab the every day at 6 o'clock in the morning. i have a /etc/backup/data.pgsql where i list all databases i want to dump, then a script in /etc/cron.daily reads this file and call pg_dump: pg_dump -U postgres $1$1.dump.sql javier ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Dump all in several files
On Tue, 15 Mar 2005 12:15:39 -0400, Alvaro Herrera
[EMAIL PROTECTED] wrote:
On Tue, Mar 15, 2005 at 05:01:39PM +0100, Frederic Massot wrote:
Lonni J Friedman wrote:
On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot
[EMAIL PROTECTED] wrote:
Hi,
On the PostgreSQL 6.5 server I use this shell script (see below) for the
backup all of the database in several files, one file per database.
Maybe i'm just not following you, but why can't you just do:
pg_dump $DBNAME $DB_NAME
where $DB_NAME is the name of each database on the box?
This script is called in a crontab the every day at 6 o'clock in the
morning.
You can obtain the list of databases for scripting with
psql -tlA | cut -d\| -f1
i didn't know that. very nice. try:
psql -Upostgres -tlA |cut -d\| -f1|xargs -i pg_dump -Upostgres -f
'{}'.dump.sql '{}'
javier
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] PostgreSQL still for Linux only?
On Tuesday 08 March 2005 07:24 pm, Tope Akinniyi wrote: Hi, I am wondering at this display of extreme Linux mentality being displayed by the 'top bras' of the PostgreSQL community. And I ask, are we encouraging Windows use of PostgreSQL at all? Take a look at tools being rolled out at PgFoundry on daily basis; all for Linux except the Windows installer. I ask myself what is being done to encourage PostgreSQL Windows users. Nothing is available to them except the Database and PgAdmin. No replication tool, no this, no that. To be honest - I wouldn't encourage the use of PostgreSQL on Win. Neither would I for any database or data warehouse application (which probably is why SAP put onto their website that they prefer linux to windows platforms). I think it could even damage the quite good reputation of PostgreSQL - if your windows box crashes and takes the DB with it - most likely it's not the fault of a lousy OS, nor the fault of an incompetent sysadmin who forgot to make backups - it will be this shitty free database system that's to blame. i think, the win version of postgresql has been a very important step, i know developers who have taken an interest in postgresql because of this version, because they first tried it on windows. later on, most of these developers migrate to linux, but if you are a windows developer it is important to have the possibility to try it first without considering using a different platform. once we built a web application using linux+apache+php+postgresql and then needed to do a demonstration of the system on the client's computer, it was really easy to get it to work with win+iis+php+postgresql. so, thank you to all the people who has made this possible. also, i don't like windows, but many developers do, they prefer windows, or they are forced to use it as a platform for their applications. so, in many ways continuing with a windows version and developing tools for windows is very important for the postgresql community. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Novice Question
On Tue, 01 Mar 2005 16:30:19 -0500, Michael Romagnoli [EMAIL PROTECTED] wrote: Sorry, I meant to ask about copying databases, not tables (including all data in the database as per below). you can do a pg_dump your_databaseyour_database.dump.sql and then createdb to create your new database, and finally pgsql -f your_database.dump.sql new_database and that's it. you should probably use -Upostgres depending on what kind of security you use. javier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Help with seq numbers...
On Mon, 14 Feb 2005 15:47:06 -0600, Cristian Prieto [EMAIL PROTECTED] wrote: why don't you use a serial? that way you don't have to insert it? i usually let postgresql take care of it, and you can use currval to return a value. That's the trouble, I need a sp that returns the user id of the last inserted user, and 0 if the username or email (another unique index) is already in the database... what about using a serial for userid, but checking first is the meail or username already exists? so, in your sp, do a: select count(*) from users where email=$1 or username=$2 and then avoid the insert and just return 0? or that would be to much load? javier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] create aggregates to concatenate
i just wanted to share this with you, i wanted to do something like this for a long time but just recently found out about create aggregate reading old posts, so here it is, using user-defined aggregate functions to concatenate results. when it's numbers i usually use SUM to compute totals, but when it's text you can create your own aggregate function to concatenate: CREATE FUNCTION concat (text, text) RETURNS text AS $$ DECLARE t text; BEGIN IF character_length($1) 0 THEN t = $1 ||', '|| $2; ELSE t = $2; END IF; RETURN t; END; $$ LANGUAGE plpgsql; CREATE AGGREGATE pegar ( sfunc = concat, basetype = text, stype = text, initcond = '' ); then, for instance to list the countries names followed by the cities in those countries as a comma separated list, you can use something like (assuming you have those tables and pais is a foreign key in... etc): SELECT paises.pais, pegar(ciudad) FROM ciudades JOIN paises ON ciudades.pais=paises.pais GROUP BY paises.pais if i'm missing something or doing something wrong please let me know, this is my first aggregate function. javier wilson guegue.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Database permissions
local is for all local connections, phppgadmin i guess is local, if run on the same computer could be local, depending on your configuration. is access and odbc on a different computer? in that case you could add host all all ipnumber mask trust to allow access to all databeses as any user for connections coming from ipnumber. in this case ipnumber being the computer with access. if you use local and ident with sameuser and run php (like needed for phppgadmin) then your web user must be allowed to access that database, meaning you should have a postgresql user with the same name and with permission to access that database. this is the obvious, so have probably tried it already, but i can't think of other answer. javier On Tue, 08 Feb 2005 20:51:29 -0800, Art Fore [EMAIL PROTECTED] wrote: More confused than ever. The pg_hba.conf file shown below was what I had originaly to get phppgadmin to work. Changed the local all all ident md5 to local all all trust and it started to working again with phppgadmin, but now, access via ODBC (with MSAccess)does not work. All I get is #DELETED for every oolumn and every row. could someone explain that to me? What should the pg_hba.conf file look like? Art Art Fore wrote: I had this working once before, but restarted the database and things went to hell. This user authentication for postgresql I will have to say is the most complex I have seen. Need a block diagram of how it works to understand it. Have .pgpass in postgres home directory, data directory is /home/postgres/data Per the md5 instructions. pg_shadow has postgres md5 password, and my password. Host computer is suse 9.2 at 192.168.121.252 I can acces phproject database from windows machine with no problem. I can access database with pgadmin3 from windows machine, no problem. Can no longer access via ODBC on windows machine Can no longer access from phppgadmin on windows machine Can no longer access from webmin on host machine. All I get is FATAL: IDENT authentication failed for user afore or for user postgres pg_hba.conf is below #local all all trust # IPv4-style local connections: host MPC all 192.168.121.0 255.255.255.0 trust # IPv6-style local connections: #hostall all ::1 :::::::trust #localall all ident sameuser local all all ident md5 host template1 all 192.168.121.0 255.255.255.0 trust host phprojekt all 192.168.121.252 255.255.255.0 trust host phpPgAdmin all 192.168.121.252 255.255.255.0 trust Any help would be welcome. Art ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] tcl and rpms for rhel3
i just build the rpm for rhel3 based on postgresql-7.4.3-3PGDG.src.rpm but i did not get the postgresql-tcl package. i looked for it in some mirrors like ftp://ftp22.us.postgresql.org/mirrors/www.postgresql.org/binary/v7.4.3/redhat/rhel3/ but apparently it is not included in the RPM version of postgresql 7.4.3 is this true? or it requires a development package i lack? has anyone else had this problem? i have rhel3 with tcl and tcl-devel installed. javier __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
