FATAL: could not reattach to shared memory (key=…, addr=…): 487

FATAL: could not reattach to shared memory (key=276, addr=01F20000): 487

Este é um erro que aparenta aparecer em algumas instalações PostgreSQL em Windows, em especial depois de existir algum update do OS. Uma instalação minha teve este problema, com o erro a ser registado periodicamente nos logs do PostgreSQL. Infelizmente o erro é problemático para aplicações dependentes da base de dados, uma vez que ele interrompe conecções entre a aplicação e a base de dados. É causador de um tipico erro de .NET com mensagem pouco esclarecedora:

[SocketException (0x2746): An existing connection was forcibly closed by the remote host]
System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) +232

Poderia ser de uma coneção externa, mas o pilha de chamadas tinha claramente a biblioteca NpgSQL – o provider PostgreSQL para .NET na lista. Não era uma conecção entre cliente e servidor, mas sim entre a aplicação e o serviço de base de dados (que é efectuado sobre TCP, mesmo estando na mesma máquina).

A solução é a actualização da instalação do serviço de base de dados – este caso passei do 8.4.0 para o 8.4.6. (a correcção deve ter sido introduzido no 8.4.1). Esta versão terá o patch para a correcção deste erro. A actualização é simples: para o serviço e correr o instalador mantendo os dados e configurações intactas (poderá ser necessário recolocar o serviço a arrancar pelo sistema utilizador LOCAL SYSTEM, se necessário).

Info sobre o erro ou patch:
http://blog.hagander.net/index.php?url=archives/149-Help-us-test-a-patch-for-the-Win32-shared-memory-issue.html#feedback
http://www.postgresql.org/docs/8.4/static/release-8-4-1.html

PostgreSQL e Linux

Cada vez mais aprecio o PostgreSQL. A base de dados é, efectivamente, muito capaz e poderosa, e felizmente não transporta a barreira das licenças que alguns outros sistemas de base de dados portam. Não é que não os justificam, e empresas que compram licenças desses sistemas reconhecem a sua importância e valor. Mas o PostgreSQL é efectivamente uma base de dados bastante simpático e funcional com um custo reduzido.

Algo que considero muito útil no Postgre é ser multi plataforma. Windows, Linux e MAC.. é escolher, que o PostgreSQL corre. O PgAdminIII, aplicação de gestão da base de dados com GUI também corre em Windows e Linux. Ainda não testei comunicação entre base de dados e servers aplicacionais suportando SOs diferentes, mas penso que é evidente o correcto funcionamento e comunicação.

Hoje estou a instalar um server em Linux, para suportar o JIRA.Tive dificuldades com o Confluence (coisa estranha de má tradução entre IIS e Tomcat dos endereços das páginas), e decidi mover as aplicações de gestão para um server dedicado. O Ubuntu é a minha “flavor” preferida (é todo o conceito…). As aplicações da Atlassian utilizam Tomcat, e tem versões que portam o serviço com elas. Pensei em o server como Tomcat server (o Ubuntu tem essa opção), mas decidi seguir a recomendação do fabricante e deixar as aplicações correr em instâncias dedicadas. O que instalei por defeito foi o clássico LAMP e a base de dados PostgreSQL.

No entanto, o PG n funciona por si só sem uma ligeira configuração. No Windows, é realizado na instalação com o Wizard, mas no Linux é mais simples / imediato com umas linhas de comando. Naturalmente o processo está mais que documento na web, mas nunca é demais reescrever.:

Para começar, duas instruções para instalar o PostgreSQL (caso n tenha sido usado a opção de instalação no processo de instalação do SO):

sudo apt-get install postgresql
sudo apt-get install pgadmin3

O primeiro pode ser ignorado caso a instalação tenha sido efectuada na instalação do SO. A segunda é um GUI de administração muito útil.

Segue então a configuração, sendo necessário inicializar o utilizador base e a password para o mesmo:
sudo -u postgres psql postgres
\password postgres

e introduza a password desejada para o utilizador postgres.

Outras coisas que podem ser feitas:
criar uma bd:> sudo -u postgres createdb [nome da base]
iniciar o serviço:> sudo /etc/init.d/postgresql-8.4 [start | stop | restart]

Finalmente, há mais dois detalhes importantes a resolver. O Postgre é, por defeito, bastante restriivo no acesso, permitindo acesso apenas por conexões vindas da própria máquina. Para aceder remotamente, primeiro deve permitir que os utilizadores da base possam autenticar-se na base na rede (caso queira esta funcionalidade). É necessário acrescentar o seguinte ao ficheiro /etc/postgresql/8.4/main/pg_hba.conf


# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all x.x.x.0 255.255.255.0 md5

onde x.x.x.0 é a definição da rede (p.e. 10.0.0.0 ou 192.168.0.0).

Para que seja possível acesso externo ao servidor de base de dados, e necessário editar /etc/postgresql/8.4/main/postgresql.conf, retirando o comentário à linha #listen_addresses = ‘localhost’
e sustituír ou acrescentar ao ‘localhost’ o ‘*’ para todas as conecções, ou uma gama de IPs para limitar. P.e.:
listen_addresses = ‘*,localhost’

E já vai bem encaminhado :D

Concatenação de resultados em PostgreSQL

Hoje enquanto tentava solucionar uma funcionalidade de uma aplicação que estou a desenvolver, surgiu a necessidade de encontrar forma de concatenar, em string, os valores retornados por várias linhas de uma pesquisa. A ideia era poder juntar num só campo, um vector léxico (tsvector do PostgreSQL) os campos texto de várias tabelas referentes à mesma entidade, para permitir uma pesquisa por texto livre centralizada.

Por exemplo supomos uma tabela simples “pessoa” com campos id_pessoa, nome e localidade (os últimos dois são varchars). Supomos também uma segunda tabela “notas”, com campos id_nota, id_pessoa, e texto, onde id_pessoa é chave externa e texto é do tipo text. Assumimos ainda que o campo notas pode ter mais que um registo por pessoa, como também pode não existir nota. A minha intenção é que consiga concatenar os vários campos de texto e vectorizar esse texto, para pesquisar nele usando o TSearch2 do PostgreSQL.

O PostgreSQL tem algumas funções úteis para isto, nomeadamente to_tsvector, COALESCE, array_to_string, array e o operador de concatenação ||.

Analisando primeiro pessoa, para concatenar o texto, teria qualquer coisa do género:

SELECT nome || ' ' || localidade FROM pessoa

o operador || efectua a concatenação do texto das colunas (que são dum tipo que represente texto). A operação anterior é efectuada para cada linha da tabela. Para vectorizar, aplico o to_tsvector:

SELECT to_tsvector((SELECT nome || ' ' || localidade FROM pessoa WHERE id_pessoa = xxx))

O to_tsvector vectoriza a string léxicamente, mas exige que a entrada seja um campo (texto) apenas, daí indicar um WHERE clause que garante um campo apenas. O segundo par de parenteses também é necessário, em torno da subquery. Teria que executar a query num ciclo qualquer para poder manipular várias linhas, individualmente.

Imagina que no campo nome tinha “Miguel da Silva Alho” e em localidade tinha “actualmente em Murtosa, Portugal”, o vector resultante seria:

'alho':4 'silv':3 'actual':5 'miguel':1 'murtos':7 'portugal':8

com a eliminação das palavras de paragem e a redução às raizes das palavras exemplificado por “actual”, “silv” e “murtos”.

Para efectuar o mesmo para as notas, a operação é semelhante, mas necessito de ter o cuidado e evitar um retorno nulo. Para tal aproveito a função COALESCE presente na maioria dos sistemas de base de dados. O COALESCE permite analisar diversos valores, retornando o primeiro não nulo.

Se tivese a certeza que apenas tinha uma linha por pessoa, seria:

SELECT to_tsvector(COALESCE(SELECT texto FROM notas WHERE id_pessoa = xxx), '')

Com COALESCE, se o SELECT retornar um valor válido, é efectivamente esse que é vectorizado. Se no entanto o resultado do SELECT for nulo, é testado a nulidade do valor seguinte, neste caso uma string vazia (mas não nula). Como é não nulo, este é vectorizado (como se fosse to_tsvector(”) ). O truque surge no entanto, se quero juntar o texto das diversas notas de uma pessoa. Nesse caso necessito de recorrer ao array_to_string() e fico com :

SELECT to_tsvector(COALESCE((
	SELECT array_to_string(
	  array(SELECT texto FROM notas WHERE id_pessoa = xxx), ' ')
	),''))

Em array, a lista de resultados é convertido para um array, e com o array_to_string, cada item do array é concatenado, separado apenas pro um espaço (como definido no segundo parâmetro da função). Novamente , tenho um COALESCE para prevenir um valor null do conjunto.

Tendo agora os dois vectores, posso concatena-los:

SELECT	to_tsvector(
		(SELECT nome || ' ' || localidade FROM pessoa WHERE id_pessoa = xxx)
	) || 
	to_tsvector(COALESCE(
               (SELECT array_to_string(
	  	  array(SELECT texto FROM notas WHERE id_pessoa = xxx), ' ')
		),'')
       )

Repara que tenho a concatenação dos vectores através de ||. se o segundo fosse nullo, o resultado seria tb nullo, independentemente do resultado do primeiro vector. Daí a importância do COALESCE.

E aí está.

Backup de PostgresSQL em C#

Para uma aplicação que estou a desenvolver com base numa BD Postgre, precisei de criar um script de backup da base de dados. A ideia é clicar num botão da interface web, e fazer o dump da BD, de forma simples, e permitir que o utilizador (que neste caso não tem nada a haver com IT) possa descarregar e archivar a base facilmente.

O método:

/// <summary>
        /// Backup Database to file (dump)
        /// </summary>
        /// <param name="server"> </param>db -> ConfigKey ["dbbackupserver"]
        /// <param name="port"> </param>db -> ConfigKey ["dbbackupport"]
        /// <param name="user"> </param>db -> ConfigKey ["dbbackupuser"]
        /// <param name="password"> </param>db -> ConfigKey ["dbpbackuppassword"]
        /// <param name="dbname"> </param>db -> ConfigKey ["dbbackupdbname"]
        /// <param name="backupdir"> </param>db -> ConfigKey ["dbbackupdir"]
        /// <param name="backupFileName"> </param>db -> ConfigKey ["dbbackupfilename"]
        /// <param name="backupCommandDir"> </param>db -> ConfigKey ["dbbackupworkdir"]
        /// <returns>The file name with the db dump</returns>
        public string BackupDatabase(
            string server, 
            string port, 
            string user, 
            string password,
            string dbname, 
            string backupdir, 
            string backupFileName,
            string backupCommandDir)
        {
            //string password = ConfigurationManager.AppSettings["dbpbackuppassword"];
            //string server = ConfigurationManager.AppSettings["dbbackupserver"];
            //string port = ConfigurationManager.AppSettings["dbbackupport"];
            //string user = ConfigurationManager.AppSettings["dbbackupuser"];
            //string dbname = ConfigurationManager.AppSettings["dbbackupdbname"];
            //string backupdir = ConfigurationManager.AppSettings["dbbackupdir"];
            //string backupFileName = ConfigurationManager.AppSettings["dbbackupfilename"];
            //string backupCommandDir = ConfigurationManager.AppSettings["dbbackupworkdir"];
            
            try
            {

                Environment.SetEnvironmentVariable("PGPASSWORD", password);

                string backupFile = backupdir + backupFileName +
                    DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".backup";
                string BackupString = "-ibv -Z3 -f \"" + backupFile + "\" " +
                    "-Fc -h " + server + " -U " + user + " -p " + port + " " + dbname;

                Process proc = new System.Diagnostics.Process();
                proc.StartInfo.FileName = backupCommandDir + "\\pg_dump.exe";
                proc.StartInfo.Arguments = BackupString;
                                
                proc.Start();

                proc.WaitForExit();
                proc.Close();

                return backupFile; 

            }
            catch (Exception ex)
            {
                throw new Exception("An unknown error occured while trying to perform the database backup/restore operation.\n\nException: " + ex.Message);
            }
        }

Basicamente, o método inicia o processo / commando da shell “pgdump” que vem com a instalação do PostgreSQL e faz um dump das tabelas e dados. Eu passo os parâmetros de nome de ficheiro e BD e directório de armazenamento etc, como parâmetro do método, porque tenho integrado numa framework e deverá servir para outras aplicações, mas podia perfeitamente referenciar as chaves da configuração directamente ou mesmo escrever o código do commando (hardcoded). No fim, ele devolve o caminho do ficheiro para enviar para a interface.