Re: [GENERAL] Move rows from one database to other
You can try OmniDB: http://www.omnidb.com.br/en_index.aspx OmniDB has a Convert feature, where you can set a data transfer, even if the target table exists. Em ter, 21 de fev de 2017 às 11:18, Adrian Klaver <adrian.kla...@aklaver.com> escreveu: > On 02/21/2017 12:53 AM, Thomas Güttler wrote: > > I want to move table rows from one database to an central database. > > You actually talking about moving from ~100 databases to the central > database, correct? > > > > > Both run PostgreSQL. > > Are all the Postgres instances the same version and what is the version > or versions? > > > > > My use case looks like this: > > > > There are N satellite databases in different data centers. N is about > > 100 at the moment. > > > > There is one central database. > > > > I need a way to reliably move rows from the satellite databases to the > > central one > > Two ways I can think of: > > https://www.postgresql.org/docs/9.6/static/dblink.html > > https://www.postgresql.org/docs/9.6/static/postgres-fdw.html > > > > > Example > > > > The rows of host1 look like this: > > > > host1, 2017-02-21, abc > > host1, 2017-02-20, def > > host1, 2017-02-19, ghi > > > > The rows of host2 look like this: > > > > host2, 2017-02-21, foo > > host2, 2017-02-20, bar > > host2, 2017-02-19, blu > > > > After syncing, all lines which were transferred should be deleted on the > > satellite databases. > > > > The central table should look like this (it has the same schema) > > > > host1, 2017-02-21, abc > > host1, 2017-02-20, def > > host1, 2017-02-19, ghi > > host2, 2017-02-21, foo > > host2, 2017-02-20, bar > > host2, 2017-02-19, blu > > Is there a Primary Key on the satellite tables or some way of > determining unique rows? > > Is there any existing overlap between the data in the central database > and the satellite databases? > > > > > > > > I don't want to code this myself, since there a tons of possible race > > conditions: > > How much data are you talking about moving from each database? > > How active are the satellite databases? > > > > > - inserts can happen during syncing. > > Can UPDATEs happen? > > > - Network can break during syncing. > > - inserts into the central table can break (e.g. disk full): No loss at > > the satellite database must happen. > > - ... > > > > How to solve this with PostgreSQL? > > > > Regards, > > Thomas Güttler > > > > > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- William Ivanski - Microsoft MVP
Re: [GENERAL] migrate Sql Server database to PostgreSql
Hi, At the company we work for, we migrated from Oracle to PostgreSQL "community edition". We have no regrets. PostgreSQL allowed us to expand, we configured it so now we have a better performance compared to before, and we even have streaming replication to mirror our main database. By the way, to perform the migration from Oracle to PostgreSQL we used OmniDB [1], an open source web tool for database management and migration, written by ourselves. OmniDB supports many well-known RDBMS system, among them Oracle, PostgreSQL and MS SQL Server. However, OmniDB can only migrate tables, data, indexes, unique constraints, primary and foreign keys. In our case, we had hundreds of procedures and functions, and they were manually translated. [1] http://www.omnidb.com.br/en_index.aspx Em 17h18 Qua, 18/01/2017, PAWAN SHARMA <er.pawanshr0...@gmail.com> escreveu: > Hello All, > > I am using postgres 9.5 enterprise edition. > > i want to to how to migrate Sql Server database to PostgreSql. what are > the things required for migration and what are the cron and prons of > migration. > > -Pawan > -- William Ivanski - Microsoft MVP
Re: [GENERAL] Importing SQLite database
OmniDB [1] is able to convert databases from one RDBMS to another. I suggest you perform a conversion from your SQLite file to a PostgreSQL database. This page [2] can help you on how to add connections to OmniDB and this other page [3] explains how to use the convert feature. [1] http://www.omnidb.com.br/en_index.aspx [2] https://github.com/OmniDB/OmniDB/wiki/3.-Creating-Users-and-Connections [3] https://github.com/OmniDB/OmniDB/wiki/10.-Schema-Conversion Em qui, 8 de dez de 2016 às 10:55, Igor Korot <ikoro...@gmail.com> escreveu: > Hi, ALL, > I have a text file which I got from exporting the SQLite database. > > The file contains an SQL statement which will generate the database. > > Excerpt from this file: > > [code] > CREATE TABLE leagues( id INTEGER AUTOINCREMENT PRIMARY KEY, name > VARCHAR(100),balance DOUBLE(10,2)); > CREATE TABLE players(playerid INTEGER AUTOINCREMENT PRIMARY KEY, > player_name VARCHAR(60), player_position CHAR(1)); > CREATE TABLE player_draft(id INTEGER, playerid INTEGER, > drafted_position CHAR(1), FOREIGN KEY id REFERENCE leagues(id), > FOREIGN KEY playerid REFERENCE players(playerid)); > INSERT INTO leagues VALUES(1, 'Test1', 260.00); > INSERT INTO leagues VALUES(2, 'Test2', 260.00); > INSERT INTO players VALUES(1, 'Player1', 'D'); > INSERT INTO players VALUES(2, ''Player2', 'F'); > [/code] > > My question would be: > Is there a command in Postgres which will open this file and execute all > those > commands one-by-one in a transaction? > Or I will have to do a manual table creation, then split this file and > use "LOAD..." > command to load the data into the tables? > > Hopefully I made myself clear. > Let me know if not and I will try to clarify further. > > Thank you. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- William Ivanski
Re: [GENERAL] Postgres Traffic accounting
Does it need to be done by listening to network packets? You can get statistics by query and user with pgbadger. Em 9h41 Seg, 05/12/2016, basti <mailingl...@unix-solution.de> escreveu: > Hallo, > > I have to try traffic accounting for postgres using tcpdump and nfdump. > I can see what traffic is produced but cant see the query / activity who > do it. because there is an ssl connection. use plain text is not an option. > > I also try to use tcap-postgres. this does not compile on my server and > its very old (year 2004). > > Is there a way to see traffic network of postgres sort by query? > > best regards > basti > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- William Ivanski
Re: [GENERAL] Trim performance on 9.5
I just ran EXPLAIN ANALYZE, please see images attached. Field doesn't have a index. Em sex, 18 de nov de 2016 às 12:16, vinny <vi...@xs4all.nl> escreveu: > On 2016-11-18 15:06, William Ivanski wrote: > > Hi, > > > > I recently did major improvements on perfomance on our routines by > > simply removing the call for trim functions on specific bottlenecks. > > Please see images attached for a simple example. > > > > I'm using PostgreSQL version 9.5.5-1.pgdg80+1 on Debian 8.6. Someone > > knows if it's a bug on trim function? Thanks in advance. > > > > -- > > > > William Ivanski > > Did you run EXPLAIN on these queries? > > I'm guessing that you have an index on the field, but not on > TRIM(field), > which would mean that the database is forced to seqscan to fetch every > row value, trim it and then compare it. > -- William Ivanski -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trim performance on 9.5
Hi, I recently did major improvements on perfomance on our routines by simply removing the call for trim functions on specific bottlenecks. Please see images attached for a simple example. I'm using PostgreSQL version 9.5.5-1.pgdg80+1 on Debian 8.6. Someone knows if it's a bug on trim function? Thanks in advance. -- William Ivanski -- 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] Strange? BETWEEN behaviour.
You could try:
select * from table where date = '2016/10/20'::date
Em qui, 20 de out de 2016 às 09:52, Bjørn T Johansen <b...@havleik.no>
escreveu:
> I have the following SQL:
>
> SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016
> 00:00:00','DD.MM. HH24:MI:SS') AND to_timestamp('20.10.2016
> 23:59:59','DD.MM.
> HH24:MI:SS')
>
> date is of type timestamp.
>
> I was expecting to get all the records that had datepart = 20.10.2016 but
> I am not getting that..
>
> What am I missing?
>
>
> Regards,
>
> BTJ
>
> --
>
> ---
> Bjørn T Johansen
>
> b...@havleik.no
>
> ---
> Someone wrote:
> "I understand that if you play a Windows CD backwards you hear strange
> Satanic messages"
> To which someone replied:
> "It's even worse than that; play it forwards and it installs Windows"
>
> ---
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
William Ivanski
[GENERAL] OmniDB 1.6
Guys, We are developing a web tool called OmniDB: http://www.omnidb.com.br/en_index.aspx OmniDB supports Firebird, MariaDB, MySQL, Oracle, PostgreSQL, SQLite, Microsoft SQL Server and Microsoft Access. OmniDB provides a unified workspace to manage databases from different DBMS in a comfortable way. It also can convert schemas between any supported databases and it's very simple to configure. Yesterday we released version 1.6, with many improvements, including support for functions and procedures on all supported RDBMS systems and full dark theme. We are looking forward to hear feedback from the community. Thanks in advance! -- William Ivanski
