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] 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] 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_database>your_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] Store procedure -------------- !!!!!!!!!!!!!! URGENTE """""""""""""""
i guess you can create a simple sql function: http://www.postgresql.org/docs/8.0/interactive/xfunc-sql.html or more complex pl/pgsql functions http://www.postgresql.org/docs/8.0/interactive/xplang.html On Tue, 22 Feb 2005 14:37:21 -0300, Adelson - Informática <[EMAIL PROTECTED]> wrote: > > Por favor gostaria de saber como utilizo store procedure no postgre ... > > não quero retornar valor , apenas que o banco de dados me execute uma > instrução ... > > Exemplo : insert into cliente (cnp,nom) values ( '21231231','CLIENTE TESTE') > > sem mais, > > Adelson > > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
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] 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])
[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